Userform - Date Autofilter

[Takes2ToTango]

Hi All,

I have a Userform that autofilters a table using wildcards and user input.

I have attempted to replicate this code for filtering the table by 'years' from dates, however all I am getting is a blank table.

Using the above userform I put in '2024'

Sheet4.ListObjects("Table").Range.AutoFilter Field:=8, Criteria1:="*" & "/" & "*" & "/" & DateTxt.Value

This code then runs and i was hoping that the table would then be filtered by all dates ending in '2024'

However I only get an empty table and no results

 

[Micron]

Your criteria is a string. AFAIK, you must use the Like operator when comparing string with wild cards. I'd try = Like "*" ... first.
If that didn't work I try using a full valid date as criteria. If your data is text data type that would be in US format as in "10/21/2024". If the data is date data type then #10/21/2024#. You could also just look for the year and forget all the wild card stuff for the rest of it. Something like Criteria1:= Year(DateTxt) = 2024 for actual dates, or perhaps ="2024" for strings.

 

[Takes2ToTango]

Thanks for your reply.

I have had a go with changing the 'Like' ** and changing the criteria to remove the wildcard but no luck. We may use multiple years so it'll have to be able to look at 2022 etc for example

 

[dmt32]

Hi,
if you are wanting to filter all dates for a year specified in your Textbox - you could try creating date range using dateserial function and pass to long variables for the Criteria.

    Dim FilterYear     As Long
    Dim StartDate      As Long, EndDate As Long
 
    FilterYear = Val(DateTxt.Value)
 
    StartDate = DateSerial(FilterYear, 1, 1)
    EndDate = DateSerial(FilterYear, 12, 31)
 
    Sheet4.ListObjects("Table").Range.AutoFilter Field:=8, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

This assumes that the dates in your table are real dates.

Dave