AutoFilter using string array not working

[BradleyS]

My Autofilter doesn’t work using the below code, but if I manually set the numbers in the array as i.e. arr = Array("18", "19", "23") it works fine.

It starts with a listbox in a userForm that pops up and allows me to select items

When I click the userform button it runs the following code to capture the position of the selecteditems and puts them in a string variable

Dim SelectedItems As String
For i = 0 To lsbItems.ListCount - 1
            If lsbItems.Selected(i) = True Then
                If IsEmpty(SelectedItems) Then
                    SelectedItems = i
                Else
                    SelectedItems = SelectedItems & "," & CStr(i)
                End If
            End If
        Next i
    'Remove any leading comma
    SelectedItems = WorksheetFunction.Substitute(SelectedItems, ",", "", 1)
    'Debug.Print SelectedItems   
    Hide
    Application.Run "Module1.SubCall"

This then calls module1 sub (shown below), which gets the selected items compiled from the above code, which should then apply the autofilter using the numbers in the string array (but it doesn’t work.)

When I look at the Autofilter settings it is written as: equals 18,19,23, which is why I assume it doesn’t work

Here is my Autofilter code:

Public Property Get GetListBoxData() As String
    
    'This grabs the selected data from the listbox and passes it to the module sub
    GetListBoxData = SelectedItems

End Property

Sub autofilter()
Dim i As Long, arr As Variant
        arr = Array(ufItems.GetListBoxData)
    Dim Rng As Range, Rng_Tag As Range, myCell As Range
    'Apply filter to data range
    Set Rng = ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion   
    'If there is a filter applied make sure it is set back to show all
    If ThisWorkbook.ActiveSheet.AutoFilterMode = True Then
        ThisWorkbook.ActiveSheet.AutoFilter.ShowAllData
    End If
    'Filter the data in column 7 (column G). Criteria1 is the string variable containing the selectedItems from the listbox
    Rng.AutoFilter Field:=7, Criteria1:=arr, Operator:=xlFilterValues
End Sub

How can I get the Autofilter to work using the number in the string variable?

 

[DanteAmor]

Sub autofilter()
Dim i As Long, arr As Variant
arr = Array(ufItems.GetListBoxData)

Two observations on that part of your code.
1. I don't see how the object is ufItems defined. (In the future, you should put all your code, that way it will be easier to help you.)
2. You should not use reserved words as procedure or variable names, the results may be unexpected. Change autofilter to, for example, proc_autofilter, or something like that.

Try the following.
To convert the text to an array, change this line:

arr = Array(ufItems.GetListBoxData)

For this:

arr = Split(ufItems.GetListBoxData, ",")

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[BradleyS]

Two observations on that part of your code.
1. I don't see how the object is ufItems defined. (In the future, you should put all your code, that way it will be easier to help you.)
2. You should not use reserved words as procedure or variable names, the results may be unexpected. Change autofilter to, for example, proc_autofilter, or something like that.

Try the following.
To convert the text to an array, change this line:

arr = Array(ufItems.GetListBoxData)

For this:

arr = Split(ufItems.GetListBoxData, ",")

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

Thank you that has done the trick. Thank you
With regards to the naming, the names I provided are not what I have actually used in my code. I only changed them for the forum. I completely agree with you on naming conventions.