Table Filter greater than and blanks at the same time

user_6139095 0
Viewed 33

I currently have an excel table which I want to filter with 2 Criteria simultaneously.

I want the table to be presented with a ">=" Criteria and with a "<>" Criteria in the same filed.

Since the table is big I want to limit the rows to be shown (Criteria ">=") and, on the other hand, to give the blank cells of the same filed as input (Criteria "<>").

The code I wrote follows:

Sub Filter()

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("PiPeLine_Table")

    tbl.AutoFilter.ShowAllData
    tbl.Range.AutoFilter Field:=7, Criteria1:=">=202100000", Operator:=xlAnd, Criteria2:="<>"

End Sub

Though my table shows like just the Criteria1 had been applied:

Field 7 represents the colored column.

Last cell with value is the one shown (202200205).

After this cell should the blank cells show.

enter image description here

Pretty sure I'm doing a basic mistake or missing something really basic but can't figure out what it is.

Hope some of you can help me out.

Rafa

listobject filter vba excel
1 Answers

When using Operator:=xlAnd, you are asking for both conditions to be true. So 202200205 is both ">=202100000" and "<>", but a blank is just "<>".

Change it to Operator:=xlOr. So if either condition is true the match will be shown.

user_75586820
Related