Macro Code to Number Lines based on if there is Da

Question

I am newish at VBA but I cannot figure out how to make this work. I am copying a ton of data from a another Excel sheet and then deleting the unneeded rows. The problem is that I need my data numbered in column A and when I delete the row it skips numbers. I want a macro that will number my data after I delete the unneeded lines. I do need it to stop when my data stops but the number of lines will be different each time. Any tips? Thank you.

Answer 1

You may try something like this...

After deleting rows, run the following code which will add the sequence number in column A starting from row2.

Sub SqNumber()
Dim lr As Long
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With Range("A2:A" & lr)
    .Formula = "=Row()-1"
    .Value = .Value
End With
End Sub

Answer 2

Use union method. You collect range and at the last line, delete them.

Sub test()
    Dim rngU As Range
    Dim i As Integer

    For i = 1 To 100
        If Range("a" & i) = "" Then
            If rngU Is Nothing Then
                Set rngU = Range("a" & i)
            Else
                Set rngU = Union(rngU, Range("a" & i))
            End If
        End If
    Next i
    If rngU Is Nothing Then
    Else
        rngU.EntireRow.Delete 'rngu is collected ranges.
    End If
End Sub