CODEWITHSUNDEEP
excelvbaautofilter
feetwet
feetwet

Reputation: 3446

Excel AutoFilter Criteria Variant to string

I'm trying to get an AutoFilter setting as a string.

I set A1:A5 in a worksheet to:

Rows
valA
valB
valC
valD

Then I AutoFilter the worksheet and select Rows values valA, valB, valC. (I.e., I have filtered out Rows value valD.)

I run the following VBA:

Sub CaptureFilters()
    With ActiveSheet.AutoFilter.Filters.Item(1)
        Cells(1, 3).Value = Join(.Criteria1)
        Cells(1, 4).Value = Replace(Join(.Criteria1), "@=", "")
    End With
End Sub

Now both worksheet cells C1 and D1 give a #NAME? error, but their formulas show =@valA =@valB =@valC.

VBA shows that .Criteria1 is an Array of Variant/Strings. Excel VBA inspection

How can I get .Criteria1 as a string value in a worksheet cell? In this example I want a cell that contains the string valA valB valC.

Upvotes: 0

Views: 448

Answers (1)

Storax
Storax

Reputation: 12167

Rewrite to

Sub CaptureFilters()
    With ActiveSheet.AutoFilter.Filters.Item(1)
        Cells(1, 3).Value2 = "'" & Join(.Criteria1)
        Cells(1, 4).Value = "'" & Replace(Join(.Criteria1), "@=", "")
    End With
End Sub

Otherwise Excel will interpret the string as formula as it starts with a =.

Or you try

Sub CaptureFiltersA()
    With ActiveSheet.AutoFilter.Filters.Item(1)
        Cells(1, 3).Value2 = Replace(Join(.Criteria1), "=", " ")
        Cells(1, 4).Value = Replace(Join(.Criteria1), "=", "")
    End With
End Sub

There are four scenarios for a text autofilter – one value, two, and more than two. To handle them all:

Sub CaptureFiltersA()
    With ActiveSheet.AutoFilter.Filters.Item(1)
        If IsArray(.Criteria1) Then  ' CASE: More than two values
            Cells(1, 3).Value = Replace(Join(.Criteria1, ","), "=", "")
        Else
            Cells(1, 3).Value = Replace(.Criteria1, "=", "")  ' CASE: One value
            If .Count = 2 Then  ' CASE: Two values
                Cells(1, 3).Value = Cells(1, 3).Value + "," + Replace(.Criteria2, "=", "")
            End If
        End If
    End With
End Sub

Upvotes: 1

Related Questions