Renumber cells in a column after rows are deleted


Start sheet (2_Main_List) - fill in the yellow Quantities column. Basic math is done for totals.

Go to the next sheet. (3_Selected_List_1) All rows data is moved if Column B is greater than 0. This works fine.

Pick the macro button.

This is where the problem is. 

Macro starts - Remove all Blank Rows. This works fine.

Problem is - Renumber Column A (1,2,3,4...) starting at row 10 to end of full cells in Column C.

I am a novice to macros with VBA. Please help. Studied and downloaded many ideas to no avail. Thank you



THANK YOU, Very Much
It worked perfectly. I don't think I could have gotten it by myself.
Steve99 (rep: 2) Jan 14, '21 at 9:49 am
Steve. With time and experience you will see such solutions, check out Dons tutorials (via the menu item on the main Forum page) for some great ideas. Thanks for selecting my answer and good luck improving your knowledge of Excel and VBA. 
John_Ru (rep: 502) Jan 14, '21 at 2:39 pm
Add to Discussion


Selected Answer


Here's a solution (in the modified file attached, code shown below)- try it from the button and it should work. Explanation follows...

You were correct to delete rows from the bottom but the problem lies in the formula in column A of Sheet 3 e.g. cell in A11 is =A10+1. If one of the cells is deleted, the cell reference is deleted too so Excel doesn't know what to do .

I've added the code in bold below to your macro. Once the empty rows are deleted, you repeat your simple For/Next loop but going forward (=down) from the first row to the new last-used row in A and set the value of the cell in A to be the counter r less 9 (so A10 would be 10-1=1, A11 becomes 2 etc.)

Sub Button1_Click()

Dim LastRow As Long, Firstrow As Long
Dim r As Long

With ActiveSheet
    Firstrow = 10
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    For r = LastRow To Firstrow Step -1
        If .Range("B" & r).Value = "" Then
           .Range("B" & r).EntireRow.Delete
        End If

    Next r

    'recalculate last row then renumber from first row up
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    For r = Firstrow To LastRow
        .Range("A" & r).Value = r - 9
    Next r

End With

End Sub

Hope this solves your problem/ answers your question.


Answer the Question

You must create an account to use the forum. Create an Account or Login