Use a macro to merge cells in a column that are the same

[Josh Craig]

Hi,

I posted earlier today but I have another related question. I have data
like this:

column A: column b:
red 1
red 2
red 4
blue 12
blue 7
green 9

I need a macro to merge the cells in column A where they have the same value
so instead i would get:

column A: column b:
red 1
2
4
blue 12
7
green 9

Any ideas?

 

[Dave Peterson]

Don't do it!

Merged cells cause nothing but trouble--try filtering/sorting/charting with
merged cells and you'll see.

If all you want to do is hide those values so it looks pretty, then Debra
Dalgleish shows how:
http://www.contextures.com/xlCondFormat03.html#Duplicate

 

[John Smith]

I just finished writing a script to sort a sheet that contains a
column of merged cells. The very last step is to merge the cells
in column A, similar to what you want to do. However, the format
of my data is different from that of yours. Instead of repeat
values in column A, mine has blanks.

Mine is like:

Column A

red
blank
blank
blue
blank
green

etc.

In my case, the very last cell in Column A may be empty. Thus, I
have to use Column B to determine the range of cells. Here is my
code. You are welcome to modify it to suit your needs.

For Each cell In Worksheets("sheet1").Range([b1],
[b65536].End(xlUp)).Cells


If cell.Offset(0, -1).Value <> "" Then
' if the cell in column A is not empty, reset counters
iCount1 = cell.Row
iCount2 = 0
Else
iCount2 = iCount2 + 1
End If

If cell.Offset(1, -1).Value <> "" Then
Range(ActiveSheet.Cells(iCount1, 1),
ActiveSheet.Cells(iCount1 + iCount2, 1)).Select
Selection.Merge
End If

Next cell

Range(ActiveSheet.Cells(iCount1, 1),
ActiveSheet.Cells(iCount1 + iCount2, 1)).Select
Selection.Merge