0

I have been trying to implement Advance Filtering using VBA with dynamic Criteria.

The Criteria Range will change based on the Filters on a Table from where I am picking the Criteria (located on a different sheet).

For example, in the below picture I need the criteria to be G5. If I change the filters on any of the columns, then the Criteria Range will be some other cells from G Column itself. The Column will always be G but the range of cells will differ.

enter image description here

Hence, I am first using a macro to select ONLY the visible cells after filtering, "Go To Special->Visible Cells" (Selection.SpecialCells(xlCellTypeVisible))

I have written the following piece of code, but it is giving the error "Criteria range not valid" for the Advance Filtering code.

Dim Criteria As Range

Sheets("<DM>Product").Select
Columns("G:G").Select
Set Criteria = Selection.SpecialCells(xlCellTypeVisible)

Range("Table2[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
    :=Criteria, Unique:=True

I am a newbie in VBA and any help will be appreciated.

Salonee Sonawane
  • 41
  • 6
  • I'd try `Set Criteria = Selection.SpecialCells(xlCellTypeVisible)` and read [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Michael Murphy Aug 24 '22 at 14:57
  • @MichaelMurphy Yes, you are right. I have now edited and corrected the question further. – Salonee Sonawane Aug 24 '22 at 15:25
  • I'd guess the `CriteriaRange` needs to be a contiguous range: if you only need to filter a single column on a list of values then you don't need `AdvancedFilter` though – Tim Williams Aug 24 '22 at 21:27
  • I am using the Advance Filter because based on this Criteria Range I need a filter on table located in another sheet. I will check out the Contiguous Range. It's like a cascade. So based on the filter on this table1 the criteria for another table2 changes. Then based on what is selected on table 2, it changes the filter on table 3 and so on. I could get the first level right. But I am stuck in making the criteria dynamic based on the first level of filter – Salonee Sonawane Aug 26 '22 at 05:12

0 Answers0