# Renumber cells in a column after rows are deleted

0

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.

### Discussion

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

0

Steve

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``````