How to add serial number in "different size merged cells" which are positioned "alternatively" in excel using VBA?

Question

As shown in image, I am trying to fill numbers in increasing order in alternate merged cells, which are different in size. So, I can't use autofill function of excel. But I want a macro so I can do it every time just hitting button once.
Note that I want numbers till the used range only.

I tried a lot to do it my self, but I am stuck now...Plz help the beginner, it's my third day in VBA.

Answer 1

This should do what you are looking for.

It will ignore non merged cells, I didn't see any in your screenshot that needed a number and were not merged so that shouldn't be an issue.

It uses column B to figure out the last row of your data.

    Dim i As Long
    Dim lr As Long
    Dim counter As Long
    counter = 1
    With Sheet1 'Change to whatever your sheets code name is
        lr = .Cells(.Rows.Count, 2).End(xlUp).Row 'If you want to use something other than column B, change the 2 to the right column index
        For i = 2 To lr
            If .Cells(i, 1).MergeCells = True Then
                If .Cells(i, 1).MergeArea.Item(1).Address = .Cells(i, 1).Address Then
                    .Cells(i, 1) = counter
                    counter = counter + 1
                End If
            End If
        Next i
    End With