VBA Merge Adjacent Dynamic Cells

[Small Paul]

Hi

I suspect there is a simple answer to this but I cannot find it!

I have a sheet with a variable number of rows. I have totals at the bottom of columns J & K. I then have a combined total in column J - offset(0,2).

I need to merge the cell in J with the adjacent cell in K.

I have tried the following (and permutations thereof):

Range("J" & Rows.Count).End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R[-2]C10:R[-2]C11)"    
    'Range(cells(ActiveCell), cells(0, 1)).Merge
    Range(0, 0)(0, 1).Merge
    
    'For Each cl In Range("J1:K500")
    'If IsEmpty(cl.Offset(1)) Then
          'Range(Selection, Selection.End(xlDown)(0, 1)).Select
            'Selection.Merge

PLEASE, don't laugh at my attempts!!!!
Any guidance would be appreciated.

Thanks
Small Paul.

 

[Norie]

Perhaps.

Dim cl As Range

    For Each cl In Range("J1", Range("J" & Rows.Count).End(xlUp).Offset(-3))
        cl.Resize(, 2).Merge
    Next cl

Note, if there is any data in column K you will get warning messages about only keeping the data in the upper left cell, i.e. column J.

 

[Small Paul]

Hi Norie

Thank you for the quick reply.
Unfortunately, it did merge the cells.
The cell in column K will always be empty.

Small Paul.

 

[Norie]

Hi Norie


Unfortunately, it did merge the cells.

I thought that's what you wanted?

 

[Small Paul]

Bad typo!
It did NOT merge the cells!

 

[Small Paul]

When I put:

cl.Resize(, 2).Merge

into 'immediate' it is giving me a "Compile Error: Type mismatch" error