Excel Filter Data with an array using variables

Question

Good day.

The following code does what I want, i.e. correctly filters my data.

ActiveSheet.Range("$A$1:$AV$791").AutoFilter Field:=5, Criteria1:=Array( _
        "Delta", "Ladner", "Tsawwassen"), Operator:=xlFilterValues

But, I want to use variables to change and filter different cities. The following code gives me the accompanying string.

cityStr = """Delta""" & ", " & """Ladner""" & ", " & """Tsawwassen"""
returns the string:  "Delta", "Ladner", "Tsawwassen"

But when I put the variable into the filter statement it doesn't do anything. It doesn't matter if I use quotes around cityStr - neither works.

ActiveSheet.Range("$A$1:$AV$791").AutoFilter Field:=5, Criteria1:=Array( _
    cityStr), Operator:=xlFilterValues

Any suggestions on how to use a variable in an array?

Thanks Les

Did you debug and check what is the final value after the value is substituted in the variable? — Techie
– Techie, Commented Jul 20, 2015 at 17:24
Yes, the variable shows as "Delta", "Ladner", "Tsawwassen". Autofilter doesn't seem to recognize this string if its in a variable. The result is that nothing is filtered and creates a filtered list with nothing in it. — Les Programmer
– Les Programmer, Commented Jul 20, 2015 at 17:40
The autofilter works fine if I use a single variable, such as deltaStr = "Delta" and I remove the Array. So the autofilter statement ends with Criteria1:=deltaStr. This works fine, its the variable in an array that I'm having trouble with. — Les Programmer
– Les Programmer, Commented Jul 20, 2015 at 17:48
@Nimesh: I placed the string "Delta", "Ladner", "Tsawwassen" into a cell. I changed my code to: dim cityStr as Object - set cityStr = ActiveSheet.cells(1,1) - Criteria1:=cityStr.Value. Unfortunately, I have the same result, which was nothing. — Les Programmer
– Les Programmer, Commented Jul 20, 2015 at 18:45

Raystafarian · Accepted Answer · 2015-07-20 18:23:40Z

3

It wants an array, so give it an array

Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim rng As Range
Set rng = ws.Range("A1:A10")
Dim CityStr() As Variant
CityStr() = Array("Delta", "Ladner", "octopus")
rng.AutoFilter field:=1, Criteria1:=CityStr(), Operator:=xlFilterValues
End Sub

Declare a variant array, fill it with your strings, and use it as criteria1. I think you have to use a variant per kb.

answered Jul 20, 2015 at 18:23

Raystafarian

3,0622 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Les Programmer Over a year ago

Thank you. That seems to have done the trick. I will try it with different variables.

Collectives™ on Stack Overflow

Excel Filter Data with an array using variables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related