Merging columns by looping

Question

I would like to batch-merge my columns.

Dim frrngr as Range
Set frrngr = .Range("E18:K26")
Dim k As Long
Dim j As Integer

For j = 1 To frrngr.Rows.Count Step 2
    With frrngr.Cells(RowIndex:=j 'Columnindex:=1)
        .Merge
        .Interior.ColorIndex = 2
        .BorderAround ColorIndex:=1, Weight:=xlThin
    End With
Next

I tried the .Merged option.

Even when the Columnindex:=1 is removed it still doesn't work.

Per the following I should be able to merge in the loop. https://www.mrexcel.com/board/threads/vba-merge-cells-loop.1081023/

Answer 1

Your code currently "merges" only single cells. With the Cells-property of a Worksheet or a Range, you can address only single cells. When giving only one parameter (eg .Cells(3), you get the nth (in this case 3rd) cell - starting to count at the top left cell. When giving 2 parameters (eg .Cells(3, 2), you address a cell by row and column number. But you always get only one single cell. For more information, see the Microsoft documentation

When you want to merge several cells, you have to specify the range that defines those cells - there are several ways to so. The following code will use the Resize-Method.

Now you missed to tell which cells you want to merge. The following will merge all cells of a row from your range frrngr

With frrngr.Cells(j, 1).Resize(1, frrngr.Columns.Count)
    .Merge
    (...)
End With

Merging 2 cells that are on top of each other would be

With frrngr.Cells(j, 1).Resize(2, 1)
    .Merge
End With

And merging 2 cells of 2 rows of that range would be

With frrngr.Cells(j, 1).Resize(2, frrngr.Columns.Count)
    .Merge
End With