Macro to show only last seven days based on a column

[shabbaranks]

Hi all..

Im trying to get filter my exported Access data to only show content from the last seven days based on a column. At the moment the sheet has data ranging from 01/01/2011 until today. What I would like to do is use a macro to just show data for the last 7 days - is this possible?

Thanks

 

[AlphaFrog]

This assumes the dates are in column A

Sub Last_7_Days()
    Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:=">=" & (Date - 7)
End Sub

 

[shabbaranks]

Thanks for that, if I assign the macro and run it I get a blank return. If I then go into the filter and change it to 1 day either way it works - any ideas why this is?

Thanks

 

[AlphaFrog]

My first guess is that your dates are Text-dates and not serial dates.

Dates And Times In Excel

There are a couple of ways you can convert en masse the text-dates to serial dates.

• Select the column with the dates.
• Select from the menu Data\Text to Columns
  • Select Delimited and Next
  • Deselect all the delimiters and Next
  • Select Column data format: Date
  • Finish

 

[shabbaranks]

Great worked a treat thank you.