Merge cells through a range

[craigy111]

I am hoping the Excel Gurus can help.

Using Excel 2007

I am looking to merge cells in a row through a range of columns:

Merge A6:H6
Merge J6:N6
Merge P6:S6

Then repeat the above for every 4th row for their respective cells.

Therefore i end up with A10:H10 merged etc etc

at the moment i have a mess of code:

Range("J6:n6").MergeCells = True
Range("J10:n10").MergeCells = True
Range("J14:n14").MergeCells = True
Range("J18:n18").MergeCells = True

This code is repeated for every merge.

Thanks in advance of any help

 

[Joe4]

How far down (how many rows) do you want it to go down?
If it is dynamic, what is the logic for determining that?
Is there one column that will always have data in it that we can use to find the last row?

 

[Joe4]

So here is code that should do what you want. I currently have it hard-coded to go down as far as row 100.
I also added a calculation that I commented out that shows you how to have automatically go down to the last row in column A with data.

Sub MyMergeCells()

    Dim myRow As Long
    Dim lastRow As Long
    
'   Find/enter last row
    lastRow = 100
    'lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False
    
'   Loop through all rows, merging every 4th row
    For myRow = 6 To lastRow Step 4
        Range(Cells(myRow, "A"), Cells(myRow, "H")).MergeCells = True
        Range(Cells(myRow, "J"), Cells(myRow, "N")).MergeCells = True
        Range(Cells(myRow, "P"), Cells(myRow, "S")).MergeCells = True
    Next myRow
        
    Application.ScreenUpdating = True
    
End Sub

 

[craigy111]

Thanks for the help Joe4 it worked a treat.

 

[Joe4]

You are welcome!