asked on

filtering with variables

hi all

i have a list box of values which i would like to exclude from the my data

if i add the following code the filter works

Range("A:AA").AutoFilter 14, "<>BGD*", xlAnd, "<>TTS*"

i've created a function to build the underlined text based on the values in the list box but when i use the variable from the function the auto filter does not work. any suggestions why?

Range("A:AA").AutoFilter 14, variable

Steve

How are you building the variable.. can you show the code for that please.

if you debug.print variable you should be able to see the "built string".
how does it look?

Soem sample data would be handy as it makes it easier to write code suited to you.

tessupport

ASKER

sub example ()

---- code-------

Call WH_FILTER(WH)

Worksheets("Version Control").Range("e1").Value = WH

---- code-------

end sub

*************************************************************************

Function WH_FILTER(WH)

Dim i As Long
Dim iEnd As Long
Dim WH1 As String

iEnd = Worksheets("Control").ListBoxWH2.ListCount - 1

If iEnd = 0 Then
WH1 = Worksheets("Control").ListBoxWH2.List(i)
WH = """<>" & WH1 & """"
End If

If iEnd > 0 Then

For i = 0 To iEnd
WH1 = Worksheets("Control").ListBoxWH2.List(i)

If i = 0 Then
WH1 = """<>" & WH1 & "*"","
End If

If i > 0 And i < iEnd Then
WH1 = WH & " xlAnd, " & """<>" & WH1 & "*"","
End If

If i = iEnd Then
WH1 = WH & " xlAnd, " & """<>" & WH1 & "*"""
End If

WH = WH1
Next i
End If

End Function

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tessupport

ASKER

Brill thanks