If you record a macro (merging 2 cells) you get something like this:
Range("A1:B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
You can modify this to do the same thing down the columns for as many cells as you like:
Dim i As Integer
For i = 1 To 100
With Range("A" + CStr(i) + ":B" + CStr(i))
.HorizontalAlignment = xlCenter
.MergeCells = True
End With
Next i
Note that I left only the horizontal alignment and merge cells instructions, the rest are irrelevant. The only major difference is that now instead of having only 1 row merged, the row is determined by hte value of "i", which loops from 1 to 100.
If you don't have a consistent number of rows in the column, you might want to replace the "100" above with the number of non-empty rows:
Dim i As Integer
Dim RowCnt as Integer
''''''''''''COUNT ALL THE NON-EMPTY ROWS IN COLUMN A
RowCnt = Application. _
WorkSheetFunction.CountA( _
Range("A:A"))
''''''''''''MAKE THE ROW COUNT THE UPPER LIMIT FOR "i"
For i = 1 To RowCnt
With Range("A" + CStr(i) + ":B" + CStr(i))
.HorizontalAlignment = xlCenter
.MergeCells = True
End With
Next i