Excel VBA Datetime Filter Hides All Rows Except Top Row

Question

I get a growing data set every week and have to filter its contents only for the last 7 days (weekly update from 11:00 am, last week, on). I want to automate the entire process via VBA but am struggling with getting the date-time autofilter to work. The date-time column looks like this (dd-mm-yyyy time):

date-time column

I set an autofilter in VBA with the following code:

Dim d1, d2, m1, m2, y1, y2 As Integer
Dim dt1, dt2 As String
d1 = Day(Date - 7)
d2 = Day(Date)
m1 = Month(Date)
y1 = Year(Date)
dt1 = d1 & "." & m1 & "." & y1
dt2 = d2 & "." & m1 & "." & y1

ActiveSheet.Range("$A$1:$CZ$99999").AutoFilter Field:=57, Criteria1:= _
    ">=" & dt1 & " 11:00", Operator:=xlAnd, Criteria2:="<=" & dt2

If I let the code run over the sheet, it will apply the filter but leave the whole sheet blank except for the top row, looking like this:

weird blank result

When I now manually enter the autofilter function in EXCEL to check what kind of filter was applied, it shows the filter exactly as I wished and upon pressing "ok" it actually applies and the right values show up:

filter is correct

So the code does insert the right filter but leaves all cells blank until I manually press "ok" in the filter function. How do I fix this, so the code correctly applies the autofilter and values show up automatically?

I found out that this problem seems to relate to the EXCEL country version. I am on a german excel, set to english. But I have no idea what that means and how to fix the issue.

Thanks in advance for the help.

If you click on the Drop-Down for the Filter, what exactly does it say that it is filtering for — Chronocidal
No boxes are ticked --> A check behind "Date Filters" --> "Between".. --> and if I click on it shows the right date+time (put a picture in the question so u can see) — ibi

Chronocidal Chronocidal · Accepted Answer · 2019-07-12T14:18:43

dt1 = d1 & "." & m1 & "." & y1
dt2 = d2 & "." & m1 & "." & y1

These are not Dates. You cannot compare them to Dates.

ActiveSheet.Range("$A$1:$CZ$99999").AutoFilter Field:=57, Criteria1:= _
    ">=" & dt1 & " 11:00", Operator:=xlAnd, Criteria2:="<=" & dt2

This is now trying to filter for Date values which are equal to the String value "5.7.2019 11:00" and equal to String value "12.7.2019". Since "5.7.2019 11:00" is not equal to "12.7.2019", this will never be True. Since Date values are not String values, it will be even less True

When you open the Filter menu up manually, it contains the text "12.7.2019" and "5.7.2019 11:00". When you click "OK", it parses these, recognises that they are supposed to be dates, and converts them to Dates. This then works.

What you need to do is convert your Strings to Dates before you use them. We also need to work around Microsoft Office's USA-centric habit of thinking "12.7.2019" means "7^th December 2019" instead of "12^th July 2019":

'VBA uses the same memory for Integer and Long, so always use Long
Dim d1 As Long, d2 As Long, m1 As Long, m2 As Long, y1 As Long, y2 As Long
'EVERY item on the row needs to be declared, not just the last one
'Any items without an "As" will default to Variant
Dim dt1 As Date, dt2 As Date

'Date is a horrible choice for a Variable name, because it is a built in Type
d1 = Day(Date - 7)
d2 = Day(Date)
m1 = Month(Date)
y1 = Year(Date)
'You forgot m2 and y2.  Very important on the 3rd January
m2 = Month(Date-7)
y2 = Year(Date-7)

'
dt1 = DateSerial(y1, m1, d1)
dt2 = DateSerial(y2, m2, d2)

'Why not just use entire columns "$A:$CZ"?
ActiveSheet.Range("$A$1:$CZ$99999").AutoFilter Field:=57, Criteria1:= _
    ">=" & cDbl(dt1 + TimeSerial(11,0,0)), Operator:=xlAnd, Criteria2:="<=" & cDbl(dt2)

Excel VBA Datetime Filter Hides All Rows Except Top Row

1 Answers