Excel 2007 vba - Create a filter for the selected name from a Listbox

Question

Hi,

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.

e.g:

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?

Regards,

Chuck

Answer 1

Try this:

    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

Answer 2

Thank you Hans!

By the way Hans, what does the ReDim do? does it initialize the array?

Thank you!

Chuck

Answer 3

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

Answer 4

Thank you Hans for the clarification you provided me.

Regards,

Chuck