How to renumber with merged cells?

[Tussas]

Hi all,

I have a database, where in column "B" of one of the worksheets some cells are merged (by 2, 3 or more) and some are not.
I want to number them in order that every single or "group-of-merged-cells" cell will have just one number.

For example, 3rd + 4th are merged, 5th is single and 5th+6th+7th are merged. This way about 10000 rows in first column.
How to number them in order?

I already have a partial solution:

Sub numberCells()
Dim i As Long, j As Long
i = 1
j = 1
Do
If Cells(i, "B").MergeArea.Rows.Count > 1 Then
Cells(i, "B").Value = j
i = i + Cells(i, "B").MergeArea.Rows.Count
Else
Cells(i, "B").Value = j
i = i + 1
End If
j = j + 1
Loop Until j > 1000
End Sub​

​

My problems:

1. Renumbering can only start in row 3. The first two rows are not numbered.
2. This is to be done only in one of the tabs called "Global List". This one will map the others through another macro.
3. It would be really nice if it would automatically run the macro with event "Insert row".

How can I adapt the code to it?

Thanks,
Tiago

 

[Joe4]

Duplicate: http://www.mrexcel.com/forum/excel-questions/890588-how-renumber-merged-cells.html

Please do not post the same question multiple times. All clarifications, directly-related follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).