Autofilter multiple criteria using a variable string

[chiidzzz]

Private Sub cmdOK_Click()
Dim lname As String
For i = 0 To lstDV.ListCount - 1
    If lstDV.Selected(i) = True Then
        lname = lname & lstDV.List(i, 0) & ","
    End If
Next
lname = Left(lname, Len(lname) - 1)
LeagueNames = lname
Set fltData = Sheet1.Range(Sheet1.Range("A1"), Sheet1.Range("T2").End(xlDown))
                With fltData
                    .AutoFilter Field:=10, Criteria1:=Array(lname), Operator:=xlAnd
                End With
Me.Hide
End Sub

lname is filled from a listbox, and this code is supposed to autofilter sheet1 column J based on lname values.
If I select only one value for lname, the filter works fine, but if I select more than one, the filter does not work, I get 0 values. Any idea what should I fix?

 

[steve the fish]

Try:

LeagueNames = Split(lname, ",")

then making:

Criteria1:=LeagueNames

 

[Fluff]

You need to change the Operator

Operator:=xlFilteronValues

 

[Fluff]

Oops
Post#3 should read

Operator:=xlFilterValues

 

[chiidzzz]

@steve the fish,
it throws run time error 13

 

[chiidzzz]

@Fluff
Still same problem

 

[Fluff]

Try

.AutoFilter Field:=10, Criteria1:=lname, Operator:=xlFilterValues

or

.AutoFilter Field:=10, Criteria1:=application.transpose(lname), Operator:=xlFilterValues

 

[chiidzzz]

Thank you for your effort, but still nothing is changing

 

[Fluff]

How about

Private Sub cmdOK_Click()
    Dim lname As String
    Dim LeagueNames As Variant
    Dim FltData As Range
    
    For i = 0 To lstDV.ListCount - 1
        If lstDV.Selected(i) = True Then
            lname = lname & lstDV.list(i, 0) & ","
        End If
    Next
    lname = Left(lname, Len(lname) - 1)
    LeagueNames = Split(lname, ",")
    Set FltData = Sheet1.Range(Sheet1.Range("A1"), Sheet1.Range("T2").End(xlDown))
    With FltData
        .AutoFilter Field:=10, Criteria1:=LeagueNames, Operator:=xlFilterValues
    End With
    Me.Hide
End Sub

 

[chiidzzz]

Solved, Thank you Fluff