Awesome behavior of date filter between US and UK Settings: VBA
I am filtering the Dates column and it works on my machine (US Regional Settings). When I submit a book to users in the UK, the same filter fails (UK Regional Settings). No error is thrown, the filter just fails and not the rows are filtered. IF I manually set the date returned in VBA code, the filter works.
'/ ALERT_DATE : named Range containing date. Cell Format is set as date.
'/ Column 5 in wksTemp contains all valid dates in correct format.
'/ Following code works fine in US Regional Settings.
wksTemp.UsedRange.AutoFilter
wksTemp.UsedRange.AutoFilter Field:=5, Criteria1:= _
"<" & wksSettings.Range("ALERT_DATE")
wksTemp.UsedRange.SpecialCells(xlCellTypeVisible).Copy wksDest.Cells(1, 1)
Application.CutCopyMode = False
'/ Same code fails with UK settings.
Tried, Value2
, Format
, DateSerial
It seems that nothing can filter the rows in the UK format.
In UK settings, if I manually set a value "<" & wksSettings.Range("ALERT_DATE")
like 28/05/2017
in Filter, it works.
Any ideas. How do I fix this to make it work for the UK and US?
source to share
Ah, release date with DateFilter :)
Easy fix, convert your date to long. Works from the beginning of VBA, or at least so it seems.
wksSettings.Range("ALERT_DATE") ---->> Clng(wksSettings.Range("ALERT_DATE"))
Try the following:
wksTemp.UsedRange.AutoFilter
wksTemp.UsedRange.AutoFilter Field:=5, Criteria1:= _
"<" & Clng(wksSettings.Range("ALERT_DATE"))
wksTemp.UsedRange.SpecialCells(xlCellTypeVisible).Copy wksDest.Cells(1, 1)
Application.CutCopyMode = False
Not tested but should work.
source to share