0

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
2
  • 1
    Can you work with a 2D array or do you need separate arrays? Commented Mar 15, 2019 at 15:22
  • I can work with a 2D array I'm just unfamiliar so I broke it out into two separate arrays. Commented Mar 15, 2019 at 15:35

1 Answer 1

1

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

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

2 Comments

Exactly what I was about to reply with. 2 dimensional array will do the trick too.
Hi I've updated the code briefly, I'm not sure how to use the second array to filter the data. How would I tell vba that if 'ar2' = "A" then use all the the corresponding numbers in 'ar' to filter. This also has to be dynamic, I can't hardcode "A" since the data will change...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.