VBA to delete rows based on date

Hi all

I have the following code to delete rows based on dates, but the datea I'm using has over 100,000 records and this is taking forever, is there a fasterway to do this than looping through every line?

    LastRow = Range("A1").End(xlDown).Row
    Do Until ActiveCell.Row = LastRow + 1
    If Range("B" & ActiveCell.Row) < StartDate Or Range("B" & ActiveCell.Row) > EndDate Then
        ActiveCell.EntireRow.Delete
        LastRow = LastRow - 1
    Else
        ActiveCell.Offset(1, 0).Select
    End If
    Loop

I'd thought about using autofilters, but I've heard they can be very unstable...?

thanks in advance

If you change your mind about autofilters.

Sub BillDoorz()
Dim startdate As Date, enddate As Date

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With

startdate = #9/15/2015#
enddate = #11/1/2015#
Range("B1:B" & Range("B" & Rows.Count).End(3).row).AutoFilter 1, ">" & startdate, xlAnd, "<" & enddate
Range("B2:B" & Range("B" & Rows.Count).End(3).row).SpecialCells(12).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

I would suggest testing it by highlighting the rows instead of deleting to see how it performs.

Hi John

thanks, but I am having issues with it as I am using UK format dates - dd/mm/yyyy

Do you know anyway to get around that?

thanks again

Does this help? Changes to US, deletes rows, changes back.

Sub BillDoorz()
Dim startdate As Date, enddate As Date

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
Columns(2).NumberFormat = "m/d/yyyy"
startdate = #9/15/2015#
enddate = #11/1/2015#
Range("B1:B" & Range("B" & Rows.Count).End(3).row).AutoFilter 1, ">" & startdate, xlAnd, "<" & enddate
Range("B2:B" & Range("B" & Rows.Count).End(3).row).SpecialCells(12).EntireRow.Interior.ColorIndex = 6
ActiveSheet.AutoFilterMode = False
Columns(2).NumberFormat = "dd/mm/yyyy;@"
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

Probably better ways though.

Hi

still not getting on with that

VBA really seems to hate UK dates!!

I'm also changing the line

Range("B1:B" & Range("B" & Rows.Count).End(3).row).AutoFilter 1, ">" & startdate, xlAnd, "<" & enddate

to

Range("B1:B" & Range("B" & Rows.Count).End(3).row).AutoFilter 1, "<" & startdate, xlOr, ">" & enddate

as I want to delete anything outside of the start and end dates

I think xlAnd should still be your operator in that case as well. Hard to tell from this side for me, but when I tested reversing them as I suggested, it worked.