Filter and select entries for a specific period say 5 Days with VBA Excel

Question

I want to filter all entries within last 5 days but I can't find where I'm doing wrong. Here is the code

Dim todayDate As Date
Dim fiveDaysAgo As Date
todayDate = Date
fiveDaysAgo = DateAdd("d", -4, todayDate)

ActiveCell.Rows("1:1").EntireRow.Select
Selection.AutoFilter
ActiveSheet.Range(Selection, Selection.End(xlDown)).AutoFilter Field:=6, Criteria1:= _
    ">=fiveDaysAgo", Operator:=xlAnd, Criteria2:="<=todayDate"
ActiveCell.Rows("1:1").EntireRow.Select

the filter doesn't work and I get NULL entries whereas if I enter the specific dates in the code it works as a charm. Any help will be just life saving. Thanks

score 1 · Accepted Answer · answered Sep 16 '21 at 06:12

You need to concatenate the variable to the string with &:

">=" & fiveDaysAgo

and

"<=" & todayDate

You might benefit from reading How to avoid using Select in Excel VBA.

Dim todayDate As Date
todayDate = Date

Dim fiveDaysAgo As Date
fiveDaysAgo = DateAdd("d", -4, todayDate)

ActiveCell.EntireRow.AutoFilter
ActiveSheet.Range(ActiveCell.EntireRow, ActiveCell.EntireRow.End(xlDown)).AutoFilter Field:=6, Criteria1:= _
    ">=" & fiveDaysAgo, Operator:=xlAnd, Criteria2:="<=" & todayDate

Filter and select entries for a specific period say 5 Days with VBA Excel

1 Answers1