Multiple filters on filtered list

T17hha

New Member
Joined
Mar 15, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I was wondering if I could ask for some assistance. I've tried various options and have struggled so will explain and provide the initial code I had put together.

I have a large spreadsheet with multiple columns, the objective is that I would have a form that sits on top of this table and based on certain responses being selected this will eventually populate a list of rows that are applicable once a button is pressed that stores the VBA code. The form is in place and I decided to use AdvancedFilter in VBA where I have criteria below the table where if something matches a particular value (e.g. =A) for that column then that column is filtered on that value (A). I would like to have the same take place for multiple different sets of columns through criteria ranges. I started with the following which works fine for initial list:

Sub Generate()
Range("A15:CG255").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("Y310:AA313"), Unique:=False
'End Sub

I then tried to incorporate the additional criteria but this only returns the filter based on the original list so the number of rows should initially filter down to, for example, 30 and then the new filter should filter this list down to 12 but I get a different number as it calculates the rows matching the second filter.

Sub Generate()
Range("A15:CG255").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("Y310:AA313"), Unique:=False
'Additional filter which was attempted
Range("A15:CG255").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("AE316:AH320"), Unique:=False
End Sub

It seems its running the filter on all the rows and not visible cells, I've tried many variations which seem to break excel and can't seem to find a method that works. Was hoping someone would be able to help!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Test on a copy. Hard to tell without a dataset.

VBA Code:
Sub Generate()
   Range("A15:CG255").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Y310:AA313"), Unique:=False
 
    ' Check if any rows are visible after the first filter
   If WorksheetFunction.Subtotal(103, Range("A15:A255")) > 0 Then
       ' Apply the second filter to the visible cells only
       Range("A15:CG255").SpecialCells(xlCellTypeVisible).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("AE316:AH320"), Unique:=False
   End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top