Filter Excel table if select cell values are true using VBA

Question

I have an Excel table with a list of multiple rating agencies in a column (Field 3, "Framework/Agency" in my book). Outside of the table, I have 5 rows, one for each agency, and a cell to the right with a drop-down menu containing only "Yes." Essentially, the only options for these five cells are "Yes" or blank. I would like to filter the table to include any agencies that are marked as "Yes" in column 2 and to exclude any agencies that are left blank. If all are blank, I would like the table to not be filtered by agency, as I have another set of macros that I am using on other columns.

An example of my other, more basic macros:

If Range("d4").Value = "" Then Range("Database").AutoFilter Field:=18
If Range("d4").Value <> "" Then .AutoFilter Field:=18, Criteria1:=Range("d4").Value

D4 is equal to another criteria that I have, and it filters field 18 in my table. My overall workbook looks something like this.

Col1       Col2

Agency 1    Yes
Agency 2
Agency 3    Yes
Agency 4   
Agency 5

Col1       Col2     Col3 (column desired to be filtered    Col4    Col5    Col6 (also filtered using another macro)
XXX        YYY      Agency 1                               ZZZ     AAA     BBB
XXX        YYY      Agency 3                               ZZZ     AAA     BBB
XXX        YYY      Agency 3                               ZZZ     AAA     BBB
XXX        YYY      Agency 1                               ZZZ     AAA     BBB

Given the circumstances above (A2:B7 in my book), I would like to see the results for only agencies 1 and 3, along with any other filters I have in my other filtering macros. As values change in column 2, I would like to see the results change as well.

Any help would be appreciated! Thank you in advance!

question from:https://stackoverflow.com/questions/65908988/filter-excel-table-if-select-cell-values-are-true-using-vba

Answer 1

Waitting for answers