Merging adjacent repeated columns with a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

forsetiky@gmail.com - 03 Apr 2007 19:09 GMT

I have a relatively large spreadsheet with numerous values repeated
(within the same column) and I would like to merge adjacent, identical
cells.

I have attempted to tweak an existing macro that deletes repetitive,
adjacent cells without any sucess. Does anyone have any suggestions?

--Chris

My existing macro for deleting these cells:

Sub RemoveDuplicates()

totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 2).Value = Cells(Row - 1, 2).Value Then
Rows(Row).Delete
End If
Next Row

End Sub

Reply to this Message

Barb Reinhardt - 03 Apr 2007 19:32 GMT

Instead of merging them, why not use a conditional format which "hides" the
duplicates with a white font.

http://www.ozgrid.com/Excel/Formulas.htm#CONDFORM

> I have a relatively large spreadsheet with numerous values repeated
> (within the same column) and I would like to merge adjacent, identical
[quoted text clipped - 17 lines]
>
> End Sub

Reply to this Message

Harlan Grove - 03 Apr 2007 19:44 GMT

forset...@gmail.com wrote...

>I have a relatively large spreadsheet with numerous values
>repeated (within the same column) and I would like to merge
>adjacent, identical cells.

...

You will almost certainly regret merging cells in Excel. Merged cells
cause many problems, and a number of menu commands, such as sorting,
won't work on ranges that contained merged cells. Also, if B5, C5 and
D5 originally contained "foobar", and you merged B5:D5, you'd DELETE
the contents of C5 and D5. Only B5 would contain "foobar". If you had
formulas that referred to C5 or D5, they might no longer return
correct results.

What do you really want to accomplish? It's a near certainty merging
cells won't do it unless you're only concerned with the appearance of
your spreadsheet.

Reply to this Message