Delete Entire Row on Every Sheet if Date Condition Matches

[c052463]

What I am trying to do is filter 2nd column(B) for every row for every sheet and delete if the condition matches; ">11/12/2019" in case of the following code.

The code would run into 1004 on the first sheet where the condition was matched.

Option Explicit
Sub TooTiredToNameThis()

Dim MySheet As Worksheet, MyRange As Range
Dim LastRow As Long, LastCol As Long
Dim ActSheet As Integer

Application.DisplayAlerts = False

For ActSheet = 1 To Sheets.Count

Set MySheet = ThisWorkbook.Worksheets(ActSheet)

With MySheet
    LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
    'LastCol = .Range("B" & .Columns.Count).End(xlToLeft).Column
    Set MyRange = .Range(.Cells(1, 2), .Cells(LastRow, 2)) '2 was LastCol
End With


With MyRange
    .AutoFilter Field:=1, Criteria1:=">12/11/2019"
    .SpecialCells(xlCellTypeVisible).Resize(.Rows.Count).Rows.Delete
End With

With MySheet
    .AutoFilterMode = False
    If .FilterMode = True Then
        .ShowAllData
    End If
End With

Next

Application.DisplayAlerts = True

End Sub

Sample Data:

Blah	Date	AnotherBlah	BlahAgain	Woof	WoofAgain	WoofWoof	LastOne
Blah1	10/12/2019	AnotherBlah1	BlahAgain1	Woof1	WoofAgain1	WoofWoof1	LastOne1
Blah2	09/12/2019	AnotherBlah2	BlahAgain2	Woof2	WoofAgain2	WoofWoof2	LastOne2
Blah3	13/12/2019	AnotherBlah3	BlahAgain3	Woof3	WoofAgain3	WoofWoof3	LastOne3

Can you please help me spot what went wrong and why?

[Mumps1]

Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    With ActiveSheet.Cells(1).CurrentRegion
        .AutoFilter 2, ">12/11/2019"
    End With
    With ActiveSheet
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

[c052463]

Thanks man my head is mush right now and you helped me get through this.

Here's the final code I did to do it to all sheets.

Sub ThanksMumps1()

Application.ScreenUpdating = False

Dim ActSheet As Integer

For ActSheet = 1 To Sheets.Count

With ActiveSheet.Cells(1).CurrentRegion
    .AutoFilter 2, ">12/12/2019"
End With

With ActiveSheet
    .AutoFilter.Range.Offset(1).EntireRow.Delete
    If .AutoFilterMode = True Then .AutoFilterMode = False
End With

Next

Application.ScreenUpdating = True

End Sub

[Mumps1]

Are you sure that it worked properly? I would think the macro should be something like this:

Sub ThanksMumps1()
    Application.ScreenUpdating = False
    Dim ActSheet As Integer
    For ActSheet = 1 To Sheets.Count
        With Sheets(ActSheet).Cells(1).CurrentRegion
            .AutoFilter 2, ">12/12/2019"
        End With
        With Sheets(ActSheet)
            .AutoFilter.Range.Offset(1).EntireRow.Delete
            If .AutoFilterMode = True Then .AutoFilterMode = False
        End With
    Next ActSheet
    Application.ScreenUpdating = True
End Sub