Question

(EXCEL vba) Problem: Trying to remove the entire row if the year is before 2019.

Current code: Range("B2").Select
Do Until ActiveCell.Value = ""

If ActiveCell.Value < 2019 Then ActiveCell.EntireRow.Delete

Else
ActiveCell.Offset(1, 0).Select

End If Loop

Code Problem: My data sets can be 1000's of rows so checking every cell is very slow.

Since the data is always in order, all I need to do is find the first entry <2019 and then select xlDown and delete everything, but I don't know how to find that cell and make it the active cell.

Answer 1

In case of any query do comment. Please rate answer as well. Thanks

Code:

Sub RemoveRowBefore2019()
Dim LastRow As Integer
Range("B2").Select
Do Until ActiveCell.Value = ""
    If ActiveCell.Value < 2019 Then
        ' to select the last row
        LastRow = Cells(Rows.Count, "B").End(xlUp).Row
        'then select the range from current cell till last cell and call delete on Range
        Range(ActiveCell, ActiveCell.Offset(LastRow, 0)).EntireRow.Delete
        'To exit from do loop after this
        Exit Do
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Loop
End Sub

Lets' say you have ranges from B2 till B600 and year less than 2019 is at B274.

Now see the output: All rows before 2019 are removed: