Dynamic Filter using VBA

[wildwally]

Having a little trouble with this, maybe a fresh set of eyes looking at it might help.

I have a sheet that is filtered through the use of a userform. when the user pushes the button the names are filtered according to their team. Everything works great when the list of names are hard coded into the VBA code. Example here:

ActiveSheet.ListObjects("CAD").Range.AutoFilter Field:=8, Criteria1:=Array( _
        "Harry", "Joe", "Jason", "Jose"), Operator:=xlFilterValues

Due to the team's changing I have created a settings sheet that the manager can assign the names to, which doesn't require it coming back to me to fix or change the hard code. So I'm trying to get this to work and it's not.

 Dim lst As String
Dim rng As Range

Set rng = Worksheets("Settings").Range("F2:F12")

lst = Empty

For Each cell In rng
    If cell <> "" Then
        If lst = "" Then
            lst = ("""" & cell & """")
        Else
            lst = (lst & ", " & """" & cell & """")
        End If
    Else
    End If

Next

ActiveSheet.ListObjects("CAD").Range.AutoFilter Field:=8, Criteria1:=Array(lst)

Any suggestions?

 

[wildwally]

Re: Dynamic Filter using VBA [SOLVED]

I was able to get it to work using info found in this:

http://www.mrexcel.com/forum/showthread.php?t=429788&highlight=autofilter+list