Delete rows with current month or forward dated VBA

[cameronb]

I am trying to write code which will search a columns of data, in which anything dated in the current month or future dated would delete the entire row. Any suggestions, many thanks.

 

[Weaver]

If you're deleting rows on the fly, always start from the bottom

Alternatively, you could use record macro, filter the data accordingly and delete the visible rows.

the first day of the current month can be found easily enough

=dateserial(year(now()),month(now()),1)

 

[VoG]

Try

Sub deldate()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("A" & i)
        If .Value > Date Or Month(.Value) = Month(Date) Then Rows(i).Delete
    End With
Next i
End Sub

 

[cameronb]

Unfortunately excelr8r, it is part of a much larger VBA project.

I see the logic VoG, and nice formula, however, the screen crashes. I am not sure how to adapt this. I have tried with screenupdating = false, but no joy.

 

[VoG]

however, the screen crashes.

Do you mean that you get an error message?

 

[cameronb]

Screen flashes for about 3 minutes, there are 10k rows of data. Then
an Error message appears runtime error 13, and highlights first start of If statement:

If .value > Date Or Month(.value) = Month(Date)

 

[VoG]

Try

Sub deldate()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = LR To 1 Step -1
    With Range("A" & i)
        If IsDate(.Value) Then
            If .Value > Date Or Month(.Value) = Month(Date) Then Rows(i).Delete
        End If
    End With
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[cameronb]

I think this is working VoG. I will test this this afternoon and get back to you.