Selected Answer
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
Hope this solves your problem/ answers your question.