Macro to Delete Dates in Col A less than Date in cell H1

[howard]

I have the year and month in Col A for eg 202001


I have written code to delete all dates in Col A < 202101


I set up a formula to extract 2021 in H1 using a value les than H1 i.e less than 202101 instead of

 .AutoFilter Field:=1, Criteria1:="<202101"

It would be appreciated if someone could kindly amend my code below to acccomodate my request above

 sub DeleteDateslessthanCertain_date()
Sheets("Imported Data").Select

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

'turn off alerts
Application.DisplayAlerts = False

'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Imported Data")

'identify the last row in column A and the last col in row 1
'then assign a range to contain the full data "block"
With MySheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
    Set MyRange = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With

'apply autofilter to the range showing only dates
'older than january 1st, 2013, then deleting
'all the visible rows except the header
With MyRange
    .AutoFilter Field:=1, Criteria1:="<202101"
    .SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count).Rows.Delete
End With

'turn off autofilter safely
With MySheet
    .AutoFilterMode = False
    If .FilterMode = True Then
        .ShowAllData
    End If
End With
 End Sub

See Link below to access workbook on onedrive

Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.

Store photos and docs online. Access them from any PC, Mac or phone. Create and work together on Word, Excel or PowerPoint documents.

1drv.ms

 

[Alex Blakenburg]

Try this:

    .AutoFilter Field:=1, Criteria1:="<" & MySheet.Range("H1").Value

 

[howard]

My Apologies Alex. I thought I had replied. Thanks very much for the help. It works perfectly