date filter on excel range using vba does not work-CodePudding

I am trying to filter between a date range in excel using vba. It is not working.

However when I perform a manual filter, it works. So I recorded the macro and applied the same code. Again, it did not work.

Date format in file is as below:

Let's say I am trying to filter out 01 to 31 July 2021.

Here's my code that does not work: Date in in A column.

startDT = ActiveSheet.txtStartDT.Text   '01-Jul-2021
endDT = ActiveSheet.txtEndDT.Text    '31-Jul-2021

For Each srcWsht In srcWbk.Worksheets
            lastSrcRow = srcWsht.Range("A1").End(xlDown).Row
                        
            srcWsht.Range("A2:A" & lastSrcRow).Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            srcWsht.Range("A2:A" & lastSrcRow).NumberFormat = "dd-MM-yyyy"
            
            srcWsht.Range("A1:O" & lastSrcRow).AutoFilter Field:=1, Criteria1:=">=" & CDate(startDT), Operator:=xlAnd, Criteria2:="<=" & CDate(endDT), visibledropdown:=True
                 
            
            srcWsht.Range("A2:O" & lastSrcRow).SpecialCells(xlCellTypeVisible).Copy
            
            wshtData.Range("A" & lastDataRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            lastDataRow = wshtData.Range("A2").End(xlDown).Row   1
            
Next srcWsht

CodePudding user response：

Your filter needs a numeric value to filter. But if you concatenate a String with a Date as you do in ">=" & CDate(startDT) it will implicitly convert your numeric date into a string to concatenate it. Depending on your default date format set in your operating system (mine is yyyy-mm-dd) this will result in something like ">=2021-07-01" which is not considered as a date but as a formula to calculate and it will subtratct 07 and 01 from 2021 and your filter does >=2013 which is not what you want.

So by converting your Date into a Double like CDbl(CDate(startDT)) you convert the date 2021-07-01 into 44378 which is the actual numeric value of the date that is stored in your cells. Now your filter does >=44378 and this will work

srcWsht.Range("A1:O" & lastSrcRow).AutoFilter Field:=1, Criteria1:=">=" & CDbl(CDate(startDT)), Operator:=xlAnd, Criteria2:="<=" & CDbl(CDate(endDT)), visibledropdown:=True

Note your values are Strings (text that look like dates) if the begin with '. If you now remove that ' Excel will convert them to numeric dates (so that you can calculate and filter them). But Excel guesses the format. So in case they are dd-MMM-yyyy Excel has it pretty clear which format it is and will guess correctly but be carefull with strings like 01/05/2022 where Excel cannot know if this is dd/mm/yyyy or mm/dd/yyyy. If you let Excel convert that it is a 50/50 chance to get it wrong or right!

If that is the case you need to split your strings that look like dates apart into day, month and year, and use DateSerial(Year, Month, Day) to convert it into a real numeric date. This way you define which format dd/mm/yyyy or mm/dd/yyyy your strings are and you do not let Excel guess (and maybe come up with the wrong one).

You might also need to do that if your month names are english 01-May-2021 but you work in another localized system (like I do in German). Because the German Excel has no idea how to convert May into a month because in German it is Mai. So Excel cannot convert that automatically and you need to split and convert that with your own function too (or it will only work in English Excels).