How to filter Custom DateTime yyyy-mm-dd hh:mm:ss with VBA?

One of the ways I apply to "learn" how to do things in VBA is to record a macro and then look at the code. I have an Excel Table with a column formated as yyyy-mm-dd hh:mm:ss.

So, I recorded filtering the column with "Text Filters", "Custom Filter", greater that 2023-01-01 00:00:01 and less than 2023-01-31 23:59:59.

This is the code I got from recording:

    ActiveSheet.ListObjects("TABLAMOVSINVT").Range.AutoFilter Field:=43, _
        Criteria1:=">=2023-01-01 00:00:01", Operator:=xlAnd, Criteria2:= _
        "<=2024-01-31 23:59:59"

I do have rows with DateTime within the above range. Excel did not correctly filtered the rows. In fact, after applying the filter, manually, I got ZERO rows as a result.

Using VBA code like

MsgBox "Data Type: " & VarType(cell. Value)

I found the cell is 'vbString' (8): String.

Knowing it is a string, I have done a lot of testing with code samples found here and I can't make it work.

I have tried with criteria as String, CDate(InitialDate$) and CDate(FinalDate$) and it does not work.

None of the following works:

FechaI$ = ">=2023-01-01 00:00:00"
FechaF$ = "<=2023-01-31 23:59:59"
'tablaMOVSINV.Range.AutoFilter Field:=43, Criteria1:=Array(CDate(FechaI$), CDate(FechaF$)), Operator:=xlFilterValues

tablaMOVSINV.Range.AutoFilter Field:=43, Criteria1:=">=" & FechaI$, Operator:=xlAnd, Criteria2:="<=" & FechaF$
Stop

tablaMOVSINV.Range.AutoFilter Field:=43, Criteria1:=">=" & Format(CDate(FechaI$), "yyyy-mm-dd hh:mm:ss"), Operator:=xlAnd, _
                                         Criteria2:="<=" & Format(CDate(FechaF$), "yyyy-mm-dd hh:mm:ss")

tablaMOVSINV.Range.AutoFilter Field:=43, Criteria1:=Array(FechaI$, FechaF$), Operator:=xlFilterValues
Stop

I would strongly appreciate your valuable advise on 2 things: a) How to manually setup the filter in Excel b) How to make the filter work with VBA.

Thanks, Ed

Solution

As for the manual filter settings, if the dates in the spreadsheet are text, you need to put an apostrophe before the date text in the text filter criterion, otherwise Excel will treat it as a date and not text.

In the case of VBA it is similar, you have to use apostrophes and if you refer to cells, they must contain properly formatted texts. If cells contain dates, VBA uses standard date format defined in the operating system, not the format used in Excel cells.

Sub Makro1()
     ActiveSheet.Range("$A$16:$A$28").AutoFilter Field:=1, Criteria1:= _
        ">='2024-01-21 22:01", Operator:=xlAnd, Criteria2:="<='2024-01-24 22:55"
End Sub

Sub Makro2()
     ActiveSheet.Range("$A$16:$A$28").AutoFilter Field:=1, Criteria1:= _
        ">='" & Range("A4"), Operator:=xlAnd, Criteria2:="<='" & Range("A9")
End Sub