Saturday, May 05, 2012 5:38 AM
I have been trying to make the filter work with no success. I have a listBox that contains copied and selected items from another ListBox in the same Userform. The resulted listBox must be used to extract data from the worksheet. I could not make it work as this is my first time working with arrays in vba.
The Listbox may contain n number of names and must be considered when filtering the worksheet.
Please help me achieve that..
Thanks in advance.
Range("AD3").SelectActiveSheet.Range("$AC3" & ":" & "$AE" & LastRow).AutoFilter Field:=2, Criteria1:=ListBox2.List(0), Operator:=xlOr, Criteria2:=ListBox2.List(1), Operator:=xlOr, Criteria3:=ListBox2.List(2), _ Operator:=xlOr, Criteria4:=ListBox2.List(3), Operator:=xlOr, Criteria5:=ListBox2.List(4), Operator:=xlOr, Criteria6:=ListBox2.List(5), Operator:=xlOr, Criteria7:=ListBox2.List(6), _ Operator:=xlOr, Criteria8:=ListBox2.List(7)
I know that the criteria1:= must contain the an array but could not figure out how?
Saturday, May 05, 2012 9:25 AM
Dim i As Long Dim n As Long n = ListBox2.ListCount - 1 ReDim arr(0 to n) As String For i = 0 To n arr(i) = ListBox2.List(i, 0) Next i ActiveSheet.Range("$AC3" & ":" & "$AE" & LastRow).AutoFilter _ Field:=2, Criteria1:=arr, Operator:=xlFilterValues
Regards, Hans Vogelaar
- Marked As Answer by chamdan Saturday, May 05, 2012 3:18 PM
Saturday, May 05, 2012 3:17 PM
Thank you Hans!
By the way Hans, what does the ReDim do? does it initialize the array?
Saturday, May 05, 2012 3:27 PM
In this example, arr is a so-called dynamic array. We don't know how many elements it will have when we write the code.
When you use Dim to declare an array, you must either specify the number of elements exactly:
Dim arr(37) As String
or omit it:
Dim arr() As String
You are not allowed to use a variable such as n: Dim arr(n) As String will cause an error message.
ReDim lets you use a variable when specifying the number of elements of an array.
ReDim arr(n) As String
will delete any existing elements (if present) and set the number of elements to the value of n (plus 1).
If you want to keep the existing elements, use ReDim Preserve:
ReDim Preserve arr(n) As String
This is very handy if you want to increase the size of an array during execution of a macro without losing the existing items.
Regards, Hans Vogelaar
Saturday, May 05, 2012 3:40 PM
Thank you Hans for the clarification you provided me.