Use a macro to merge cells in a column that are the same
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
Josh Craig wrote:
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?
|