EXCEL VBA find max date and filter x days before max date

Question

I have excel sheet with dates in column A, and I need to find latest date and filter last 7 days before that latest date. I only found vba codes online which takes todays date - 7 days but I cant use that because these dates are from last year.

Please and thank you!

EDIT:

Thanks for your suggestion. I tried to put it in my vba code but it filters 0 results.

Sub Filter()
    Dim maxDate As Date
    maxDate = WorksheetFunction.Max(ActiveSheet.Range("A:A"))
    ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:= _
        ">" & maxDate - 6
End Sub

Doesn't work since this VBA macro filers date by dd.mm.yyyy. (NOTE THE DOT at the end), 0 results in my case, it should filter by dd.mm.yyyy (without dot). I dont know how to filter without dot

EDIT2: This worked for me.

Sub Largest()
    Dim rng As Range
    Dim dblMax As Double
    Set rng = ActiveSheet.Range("A:A")
    dblMax = Application.WorksheetFunction.Max(rng)
    ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:= _
            ">" & dblMax - 7, Operator:=xlAnd
End Sub

FYI - You can take a date and "do math" on it, if you have 5/30/2018 in A1, in B1 you can type =A1-5 to get 5/25/2018. — BruceWayne
Yes, dates can be tricky with Excel VBA AutoFilter. Some find it easier to just loop through the array to be filtered. Sometimes I have found that one can use the numeric value of the date, as you did when coercing the date to a Double — Ron Rosenfeld
Instead of "this worked for me" why not post your own Answer to this Question so this is a complete Q&A for others? — user1531971

FunThomas FunThomas · Accepted Answer · 2018-10-16T15:40:08

To get the max value in a Range (and a column is a Range) in VBA, use worksheetFunction.Max, eg

Dim maxDate as Date
maxDate = worksheetFunction.Max(ActiveSheet.Range("A:A"))

Be careful to select a Range with consistent data, so don't mix up dates and numbers.

From there, you can simply subtract 7 from your date (or use DateAdd(d, -7, maxDate) and go ahead with the code you already found.

Edit: To use the date as filter value, change the data type of maxDate to Double. You can check this by set your filter manually and enter the following statement in the immediate window (Ctrl+G):

? ActiveSheet.AutoFilter.Filters(1).Criteria1

This results in (value will be different for you) >43329. Background is that Excel/VBA stores dates as Double.

EXCEL VBA find max date and filter x days before max date

1 Answers