VBA - Deleting all rows in data for all past dates

[monkeyhalf]

Hi all,

Need help with a macro
Essentially we have a sheet of data, changes every day.
Many columns but Column C contains the column of end dates.
I want to filter the data, and then delete every row in the data where column c contains dates before today (the day we run the macro, so changing daily)
Is this possible? I tried messing around with it but I couldnt get it to work.

I'm in Australia so my system dates are dd/mm/yyyy if that matters...from googling online it seems Excel does dates as US format?
Thankyou very much, let me know if you need more information or examples

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this with a copy of your workbook.

Sub DeletePriorDates()
  With Range("C1", Range("C" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="<" & CLng(Date)
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA - Deleting all rows in data set for all past dates

Hi all, Need help with a macro Essentially we have a sheet of data, changes every day. Many columns but Column C contains the column of end dates. I want to filter the data, and then delete every row in the data where column c contains dates before today (the day we run the macro, so...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[monkeyhalf]

Sorry about this! Will remember in future. Thankyou

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA - Deleting all rows in data set for all past dates

Hi all, Need help with a macro Essentially we have a sheet of data, changes every day. Many columns but Column C contains the column of end dates. I want to filter the data, and then delete every row in the data where column c contains dates before today (the day we run the macro, so...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[monkeyhalf]

Welcome to the MrExcel board!

Try this with a copy of your workbook.

Sub DeletePriorDates()
  With Range("C1", Range("C" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="<" & CLng(Date)
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

Amazing, thankyou very much, this worked!! Really appreciate it.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

(Might be a good idea to let the other forum(s) know that you have a solution here)