CODEWITHSUNDEEP
vbaexcel
cyboashu
cyboashu

Reputation: 10433

Weird date filter behavior between US and UK Settings : VBA

I am applying filter to a column of Dates and its working fine on my machine (US Regional Settings). When I send the workbook to users in UK, the same filter fails (UK Regional Settings). There is no error, the filter simply fails and not 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 nothings seems to Filter the rows in UK format.

In UK settings, If I manually set the value of "<" & wksSettings.Range("ALERT_DATE") for example 28/05/2017 in the Filter, it works.

Any ideas. How to rectify this so that it works for UK and US both?

Upvotes: 3

Views: 101

Answers (1)

teddy2
teddy2

Reputation: 390

Ah, the Date issue with DateFilter :)

Simplest fix, convert your date to long. Works since beginning of VBA or at least I think so.

wksSettings.Range("ALERT_DATE") ---->> Clng(wksSettings.Range("ALERT_DATE"))

Try this:

  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

No tested, but should work.

Upvotes: 3

Related Questions