Excel VBA Merge Cells for a Table run time error"1004"

V.

I included the Original Table without the Module.

Can you insert the module with your code, and see if the transfer is working

Thank you

https://onedrive.live.com/edit.aspx?cid=5b91884861232582&page=view&resid=5B91884861232582!977&parId=5B91884861232582!718&app=Excel

I merged the cells in original table itself.

Can you put the output in new table so that I can generate that output through VBA?

I inserted your macro in module 1 and I don't see the transfer in new table. The Final Copy Module 1- Copy.xlsm

with the Module 1 is in one drive .

https://onedrive.live.com/edit.aspx?cid=5b91884861232582&page=view&resid=5B91884861232582!975&parId=5B91884861232582!718&app=Excel

Here is the macro. File can be downloaded from https://1drv.ms/x/s!AldvjX7HG_m7gUYczQqCHoInx2p...

Option Explicit

Sub MergeCells()
    Dim LastRow As Long, i As Long, j As Long, k As Long
    Dim Ws As Worksheet
    
    Set Ws = ActiveSheet
    LastRow = Ws.Cells(Rows.Count, 1).End(xlUp).Row
    'If there is no data, leave Sub
    If LastRow <= 2 Then
        MsgBox "Sheet has no data"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    j = 1
    For i = 3 To LastRow
        With Ws
            If .Cells(i, "A") = .Cells(i - 1, "A") Then
                If .Cells(i, "B") = .Cells(i - 1, "B") And .Cells(i, "B") = "Closing Balance" Then
                    If j = 1 Then
                        k = i - 1
                    End If
                    j = j + 1
                End If
                Else
                If j >= 2 Then
                    .Cells(k, "A").Resize(j).Merge
                    .Cells(k, "B").Resize(j).Merge
                    j = 1
                End If
            End If
        End With
    Next i
ExitSub:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub