excelvbaautofilter

AutoFiltering two separate block of data in different rows

In a worksheet I'm working on a the moment there are two sections that need to be filtered to show when data is in the cells.

The first section has data in column B and is working fine using the code

With ActiveSheet.Range("$A$48:$D$507")
    .AutoFilter Field:=2, Criteria1:="<>"
End With

The next handful of rows have data sitting in column A, but applying the same code as above doesn't seem to include the rows that have data in them. The code I've used is

With ActiveSheet.Range("$A$508:$D$537")
    .AutoFilter Field:=1, Criteria1:="<>"
End With

What needs to be changed or added to get the second block of data showing?

Solution

  • As another user commented, you cannot have autofilters on multiple ranges on one sheet (try doing it manually without VBA - if you try to apply a 2nd filter it will remove the 1st one).

    However you can have autofilters on multiple tables on one sheet. Convert your ranges to tables first, then filter the tables like below:

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:="<>"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:="<>"