xl2002 - AutoFilter from Criteria from Other Sheet...

[Trevor Williams]

I am trying to apply an AutoFilter to records on "Sheet 2" using criteria
chosen from 7 lists on "Sheet 1"

The filtering works if each criteria contains a value, but if one is left
blank no records are shown.

I have tried adding the value "(all)" as a default into the lists on Sheet 1
but that doesn't work either.

How can I skip 'blank' entries when filtering using code? (current code
below)

Thanks

Trevor

Sub FilterResult()

myFilter = Array("ctDescription", "ctMarket", "ctPOB", "ctChannel", _
"ctCategory", "ctQuality", "ctCompetition")

For i = 0 To 6
x = myFilter(i)
Selection.AutoFilter Field:=i + 1, Criteria1:=Range(x), _
VisibleDropDown:=False
Next i

End Sub

 

[Tom Ogilvy]

Sub FilterResult()

myFilter = Array("ctDescription", "ctMarket", "ctPOB", "ctChannel", _
"ctCategory", "ctQuality", "ctCompetition")

For i = 0 To 6
x = myFilter(i)
set rng = Range(x)
if rng.Value <> "" then
Selection.AutoFilter Field:=i + 1, Criteria1:=Range(x), _
VisibleDropDown:=False
else
Selection.AutoFilter Field:=i + 1, _
VisibleDropDown:=False
end if
Next i

End Sub

 

[Trevor Williams]

Excellent, Thanks Tom

Trevor

Sub FilterResult()

myFilter = Array("ctDescription", "ctMarket", "ctPOB", "ctChannel", _
"ctCategory", "ctQuality", "ctCompetition")

For i = 0 To 6
x = myFilter(i)
set rng = Range(x)
if rng.Value <> "" then
Selection.AutoFilter Field:=i + 1, Criteria1:=Range(x), _
VisibleDropDown:=False
else
Selection.AutoFilter Field:=i + 1, _
VisibleDropDown:=False
end if
Next i

End Sub