VBA excel: Filtering By date and assigning Criteria2 value to a variable?

Question

I have a table with a column that has dates I am filtering through. I am trying to take the criteria value and assign it to a variable but keep running into an error. (Run-time error '1004': Application-defined or object-defined error). Can anyone help?

filter code:

ActiveSheet.ListObjects("MyTbl").Range.AutoFilter Field:=5, _
    Operator:=xlFilterValues, Criteria2:=Array(0, "10/1/2017")

My Code:

Dim x As Variant

If Table.AutoFilter.Filters(5).On Then

    x = Table.AutoFilter.Filters(5).Criteria2
    
End If

user_5502774 · Answer

Try this:

Dim lo As ListObject ' optional declaration
Set lo = ActiveSheet.ListObjects("MyTbl") ' optional variable assignment

If Not ActiveSheet.AutoFilterMode Then
     ActiveSheet.Range("MyTbl").AutoFilter
End If

With lo.Range
    .AutoFilter Field:=5, _ 
    Operator:=xlFilterValues, _ 
    Criteria2:=Array(0, "10/1/2017")
End With

user_1140579 · Answer

Is this what you are trying?

Let's say our data looks like this

My table name is Table1 and it is in Sheet1. Change the below code as applicable.

Code

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim Table As ListObject
    
    Dim x As Variant
    Dim Ar(1 To 3) As Date
    
    Ar(1) = DateSerial(2017, 1, 10)
    Ar(2) = DateSerial(2017, 2, 13)
    Ar(3) = DateSerial(2017, 2, 15)
    
    Set ws = Sheet1
    Set Table = ws.ListObjects("Table1")
    
    With Table
        .Range.AutoFilter Field:=5, Operator:=xlFilterValues, Criteria1:=Ar
         
         x = Join(Table.AutoFilter.Filters(5).Criteria1, ",")
         
         MsgBox x
    End With
End Sub

In Action

VBA excel: Filtering By date and assigning Criteria2 value to a variable?

2 Answers