Macro to Delete Dates in Col F which are Less than Date Specified in Col L

[howard]

I have dates in Col F in format dd/mm/yyyy from row 2 onwards and a specified date in Col L1 in format mmm yyyy eg Nov 2020

Where the Date in Col L1 < the dates in Col F, I would like the rows in Col F drom row 2 owards to be deleted where L1 is less than those dates in Col F


See my code below

 Sub Delete_RowsBelowMinDate()

               
        With Sheet1.[A1].CurrentRegion
                .AutoFilter 6, "< L1"
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

End Sub

Your assistance is most appreciated

 

[maabadi]

Try this:

Sub Delete_RowsBelowMinDate()
       With Sheet1.[A1].CurrentRegion
                .AutoFilter 6, "<" & [L1]
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

End Sub

 

[howard]

Thanks for the help. The Dates prior to Date in L1 is not being deleted


See link of file in Drop Box

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Please test and amend accordingly

 

[maabadi]

I see error page when I want open file.

 

[howard]

See new link below

Dropbox - File Deleted - Simplify your life

www.dropbox.com

going to be going out for a few hours. Will check post later

 

[maabadi]

The Date you write at L1 = Nov 2020 = 11/1/2020
When you run code the Rows have less than that date Deleted.
I test your file & don't see any problem.

AND One thing I realized:
Are you want Dates More (or Equal) than L1 Deleted Because you told

Where the Date in Col L1 < the dates in Col F

Rows Deleted. then you should change operator to Greater Than (">") or (">=").

 

[vcoolio]

Hello Howard,

A slight modification to Maabadi's code should do the trick for you:-

Sub Test()
      
          With Sheet1.[A1].CurrentRegion
                .AutoFilter 6, "<" & Format(Range("L1"), "MMM/YYYY")
                .Offset(1).EntireRow.Delete
                .AutoFilter
          End With
End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[howard]

Thanks Vcoolio, code works perfectly

 

[howard]

Hi Mabaadi

I wanted dates less than the date in L1 to be deleted

The code provided by Vcoolio worked perfectly

 

[maabadi]

You're Welcome & Glad We can help.