CODEWITHSUNDEEP
vbaexcel
H D
H D

Reputation: 554

Passing Excel rows as an array to autofilter

In sheet1, I have 4 rows with header "Company Name" with values as "Google", "Microsoft",Yahoo" now in sheet2 I need to set a filter with these 3 values

ActiveSheet.Range(A1:AC20).AutoFilter Field:=4, Criteria1:=Array("Google", "Microsoft","Yahoo"), Operator:=xlFilterValues

But now I want it to be dynamic so that I will create an array from the range in sheet1 with company names, and this list is passed in sheet2 for filtering.

Something like 

comp_name_array = Range(A1:A5)

this will create array with all company names and then pass this array in 

ActiveSheet.Range(A1:AC20).AutoFilter Field:=4, Criteria1:=Array(comp_name_array), Operator:=xlFilterValues

Upvotes: 2

Views: 1073

Answers (1)

Rory
Rory

Reputation: 34045

You need to transpose that range to make a 1D array. THen because the variable is an array, you don't need the Array() function:

comp_name_array = Application.Transpose(Range(A1:A5).Value)
ActiveSheet.Range(A1:AC20).AutoFilter Field:=4, Criteria1:=comp_name_array, Operator:=xlFilterValues

Upvotes: 2

Related Questions