CODEWITHSUNDEEP
vbaexcelloopsmerge
beks123
beks123

Reputation: 55

Merge & center every 2 cells in a range in VBA

I am trying to figure out how to merge every 2 cells in a column. I feel like I am overthinking it though. My if then statement isn't necessary I was just playing around. Here is the code: 

Dim RgToMerge As String

   For i = 1 To ActiveSheet.Cells(Rows.Count, 3).End(xlUp).row
   RgToMerge = ""
   If Cells(i, 3) = "" Then
   RgToMerge = "$C$" & i & ":$C$" & i + 1
   With range(RgToMerge)
       .Merge
       .HorizontalAlignment = xlCenterAcrossSelection
       .VerticalAlignment = xlCenter

   End With
   Else
   End If
Next I
End sub

Upvotes: 0

Views: 15318

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

Take out the if-statement and make these subtle changes

Dim RgToMerge As Range


For i = 3 To ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row Step 2

    Set RgToMerge = Range(Cells(i, 3), Cells(i + 1, 3))
    With RgToMerge
        .Merge
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlCenter
    End With

Next i

Step 2 will loop through every other row. Step 3 would do every third row, etc.

Upvotes: 2

Related Questions