Hi,
I have wrote the following VBA Code which performs two filters in a spreadsheet.
Sheets("AAA").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$BP$1005").AutoFilter Field:=1, Criteria1:="Manager"
ActiveSheet.Range("$A$1:$BP$1005").AutoFilter Field:=67, Criteria1:=Range("BP1").Value
The First will always produce a result, because there is a "Manager" always a manager for every retail store, but the second filter (a department filter), filters from those managers according to the department selected in cell "BP1". However, not every department has a Manager, so when it trys to filter on the value BP1, there may not be any results found, so it just displays everything.
Is there away I can say that if the value in BP1 does not exist in the column (Autofilter Field 67) then don't perform the the second filter?
Many Thanks,
Cris
I have wrote the following VBA Code which performs two filters in a spreadsheet.
Sheets("AAA").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$BP$1005").AutoFilter Field:=1, Criteria1:="Manager"
ActiveSheet.Range("$A$1:$BP$1005").AutoFilter Field:=67, Criteria1:=Range("BP1").Value
The First will always produce a result, because there is a "Manager" always a manager for every retail store, but the second filter (a department filter), filters from those managers according to the department selected in cell "BP1". However, not every department has a Manager, so when it trys to filter on the value BP1, there may not be any results found, so it just displays everything.
Is there away I can say that if the value in BP1 does not exist in the column (Autofilter Field 67) then don't perform the the second filter?
Many Thanks,
Cris
Last edited: