CODEWITHSUNDEEP
excelvba
iLL-Army
iLL-Army

Reputation: 41

Filter Multiple Variable Values in Loop

I am trying to use an array to filter multiple values without hardcoding the data. I would like to create a loop that will go through a list that has variable sizes (1 criteria or 3 criteria) for each condition. Here's a sample of what the data will look like:

 A    B
100   A
200   A
300   B
400   B
500   B
600   B
700   C

I would like the code to take all the values associated with 'A' - So, 100 and 200 to be used as the values to filter by, then use all of the values associated with 'B' - 300, 400, and 500 and etc..

Sub FilterMulti2()
Dim i As Integer
Dim ar(1 To 20) As String

For i = 2 To ThisWorkbook.Worksheets("Sheet1").Range("A10000").End(xlUp).Row + 1

ar(i - 1) = Sheet1.Range("A" & i - 1)
Next i

ThisWorkbook.Worksheets("Sheet2").Range("A1").AutoFilter 4, ar, xlFilterValues
'Code****

ThisWorkbook.Worksheets("Sheet2").Range("A1").AutoFilter
End Sub

Currently my code only works for one list, but I need to further make it so the code differentiates which items to filter for.

Thanks

EDIT: So I've updated the code after looking at the article @badja had posted. Here's the code: It will store the the 'A's and 'B's in ar2 but I don't know how I use that as a criteria on which items the first array will use to filter.

Sub FilterMulti2()
Dim i As Integer
Dim ar(40) As Variant
Dim ar2(40) As Variant
For i = 2 To ThisWorkbook.Worksheets("Sheet1").Range("A10000").End(xlUp).Row + 1

ar(i - 1) = Sheet1.Range("A" & i - 1)
ar2(i - 1) = Sheet1.Range("B" & i - 1)
Next i

ThisWorkbook.Worksheets("Sheet2").Range("A1").AutoFilter 4, ar, xlFilterValues
'Code****

ThisWorkbook.Worksheets("Sheet2").Range("A1").AutoFilter 'Turn autofilter Off
End Sub

Upvotes: 0

Views: 282

Answers (1)

Badja
Badja

Reputation: 875

Use something like tihs

Dim ar(1 to 20, 1) As String

This will make a "table" 21 down and 2 across

I haven't used these arrays before, but basically they're fancy tables

Please view this resource for an amazing article on multidimensional arrays

Upvotes: 1

Related Questions