VBA code for Autofilter

[jamesexcel1970]

Hello

I have attatched excel file with code which contains dummy data of course.

1) what i wanted is from A column wanted to filter starting with "A" and "B" filter which is working fine.

ActiveSheet.Range("$A$1:$C$9").AutoFilter Field:=1, Criteria1:="=a*", _
Operator:=xlOr, Criteria2:="=b*"

2) From Second column i wanted to filter with 100 which is also working fine.

ActiveSheet.Range("$A$1:$C$9").AutoFilter Field:=2, Criteria1:="=100"

3)from third column wanted to filter wanted to filter "a160" which is a code.
ActiveSheet.Range("$A$1:$C$9").AutoFilter Field:=2, Criteria1:="=a160"

Which is not existing in the list and wanted to ignore.

Problem is 1st and 2nd point working but in 3rd case as criteria a160 is not available it should ignore but result is show all blank rows.

when you dont find a value it should show the results of first and second filer atleast by ignoring third filter as it is not available.

Hope my request is clear.

Cheers!

 

[Monty]

Hey James.

Quite interesting, Never though of this.

first filter is working as values is available.
Second filter is working as values is available.
Third filter is not working as values not available..


Show you are worried about when we have first and second filter shows data because of third filter showing blank, but you wanted to retain the data of first and second though third filter is blank.

Correct me if am wrong.

 

[jamesexcel1970]

Hey little champ.

You are right when it can show data for first and two as value is existing because of third showing blank rows..which is not right.

Cheers!

 

[Monty]

James
Trying but getting same results...Getting blank n third filter...Never worked on this case.

 

[Chihiro]

On thing, third filter should have Field:=3.

To work around no data showing, you can do something like below.

Sub Macro1()
'Rest of your code
'Third filter
ActiveSheet.Range("$A$1:$C$9").AutoFilter Field:=3, Criteria1:="=a160"
'Check if there's data returned by checking visible cell count and remove criteria
If ActiveSheet.Range("$A$1:$C$9").SpecialCells(xlCellTypeVisible).Count = 3 Then
    ActiveSheet.Range("$A$1:$C$9").AutoFilter Field:=3
End If

End Sub

Alternately, you can check if a160 is present in visible cells prior to applying 3rd filter.