How to get text from the date filter criteria


Hi all,

any idea how I can get the text from the criteria of a date filter range selection in vba?

example: filter in column registration date has 2 selections1/5/2019, 2/5/2019.

I want to get these two dates.

I got this to work for non-date filters but what about this case?





I entered this formula in A2  =TODAY() + ROW() and copied down to A13. Now the code below sets the filter, reads the set filter criteria and prints them to the Immediate window.

Private Sub GetFilter()
    ' 25 Oct 2019

    Dim Ws As Worksheet
    Dim Rng As Range
    Dim Arr As Variant
    Dim f As Integer

    Set Ws = ActiveSheet
    Set Rng = Ws.Range("$A$1:$A$13")
    Rng.AutoFilter Field:=1, _
                   Operator:=xlFilterValues, _
                   Criteria1:=Array(2, "10/27/2019", 2, "10/28/2019", 2, "11/10/2019")
    Arr = Ws.AutoFilter.Filters(1).Criteria1
    For f = 1 To UBound(Arr)
        Debug.Print f, Arr(f)
    Next f
End Sub

Of course, you could skip the part of the code which sets the filter and just read the criteria which were set manually or by another macro. Observe, however, that the range must be known and that the first filter will be read if more than one filter is set for that range.

26 Oct 2019 - Edit in response to your further elucidation in the Discussion below.

The problem occurs because a date is a number which is displayed as a date string. Say, today is 26/10/2019 or 10/26/2019 - depending upon your regional settings. To Excel, however, today is 43764 which can be displayed in whichever format you choose. When you set the filter the text string (26/10/2019 or 10/26/2019) is added as filter criterium. Of course, that doesn't exist in your table and that causes the error, to wit, that a criterium is selected for filtering which doesn't exist in the list.

To work around this make sure that your dates are entered as strings, not as true dates.



ok first of all thx for the reply!
now i think i forgot to mention something important: i want to filter within excel data-tables, list objects so i dont have to specify range. so i got an excel data-table A, B, C columns like the one you created. if no date fields exist on the table the following code works sweet no matter how many criteria/filters i apply
Private Sub test()
Dim Value As Variant
Dim c As Integer
Dim txt As String
With Worksheets("Sheet1").ListObjects("Table1").AutoFilter
If .FilterMode Then
    For c = 1 To .Filters.Count
        If .Filters(c).On Then
            If IsArray(.Filters(c).Criteria1) Then
                For Each Value In .Filters(c).Criteria1
                    txt = txt & Value & ", "
                txt = txt & .Filters(c).Criteria1 & ", "
                On Error Resume Next
                txt = txt & .Filters(c).Criteria2 & ", "
                On Error GoTo 0
            End If
        End If
End If
End With
End Sub

the moment i change say column A to have dates the code errors in any line with .Filters(c).Criteria1 throwing an app defined or object defined error! 

any ideas?
Stelix Oct 25, '19 at 12:45 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login