vba to Filter Table with Dates from cell ref

[Steven101]

Hi, I need to filter a table based on 2 criteria, >= 1st date AND <= to 2nd date. Both dates are located in a cell ref in the format dd/mm/yyyy.


I have the following code

Dim rCrit1 As Range, rCrit2 As Range

Set rCrit1 = Sheets("MI").Range("B2")
Set rCrit2 = Sheets("MI").Range("B3")

Sheets("Appeals").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1
    Range("Table1[[#Headers],[Date of " & Chr(10) & "Appeal]]").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        ">=" & CDate(rCrit1.Value), Operator:=xlAnd, Criteria2:="<=" & CDate(rCrit2.Value)

The issue is that when I check the filter, it's putting the filtered dates in the format mm/dd/yyyy

I've tried changing the format of the 2 cells to mm/dd/yyyy but this doesn't work.

Any help would be appreciated.

Thanks

 

[Worf]

Hi

I would use advanced filter. The table column being filtered has the header "dates".

Sub AdvFilter()
Dim t As ListObject
Set t = ActiveSheet.ListObjects(1)
t.Range.AdvancedFilter xlFilterInPlace, [a1:b2], , 0
End Sub