loop to merge cells

[tonyjyoo]

I need a VBA that goes down 1 single column, merges with blank cells (rows may vary), then loops until finished.

For example:

A

B

C

D

<tbody>
</tbody>

A would need to be grouped with the blank cell below, same with B, C would need to be merged with the 3 blank cells below, etc.

Any help appreciated.

Thanks.

 

[Rick Rothstein]

Assuming the data you want to merge is in Column A, give this macro a try (note that it calculates the last data row from among all your columns and uses that to set the merge range for the last data item in Column A)...

Sub MergeCellsInColumnA()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1).Resize(Ar.Count + 1).Merge
  Next
End Sub

 

[tonyjyoo]

Assuming the data you want to merge is in Column A, give this macro a try (note that it calculates the last data row from among all your columns and uses that to set the merge range for the last data item in Column A)...

Sub MergeCellsInColumnA()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1).Resize(Ar.Count + 1).Merge
  Next
End Sub

Perfect.Thank you as always Rick!