tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,834
- Office Version
- 365
- 2019
- Platform
- Windows
I am trying to create a custom filter but am stuck on the logic.
Assume I only have two criteria (the real version has many more): Fruit and Location.
Cells A1 and B1 contain data validation. The data starts from row 10.
If the user selects a particular Fruit and Location, I only want rows that satisfy that combination to be shown - that I can do.
What I find tricky is the data validation also contains an "All" value. So if the criteria for Fruit was "All" and Location was "UK", I want all rows where Location is "UK" to be shown. The Fruit criteria is relaxed.
How would the code for this look like? As I said, if it's only two criteria, it's relatively straightforward but in reality, I have many more criteria and the code becomes very long.
Thanks
Assume I only have two criteria (the real version has many more): Fruit and Location.
Cells A1 and B1 contain data validation. The data starts from row 10.
If the user selects a particular Fruit and Location, I only want rows that satisfy that combination to be shown - that I can do.
Code:
Dim Counter As Long
For Counter = 10 To 15
If Sheet1.Cells(Counter, 1).Value = Sheet1.Cells(2, 1).Value And _
Sheet1.Cells(Counter, 2).Value = Sheet1.Cells(2, 2).Value Then Sheet1.Rows(Counter).Hidden = False
Next Counter
What I find tricky is the data validation also contains an "All" value. So if the criteria for Fruit was "All" and Location was "UK", I want all rows where Location is "UK" to be shown. The Fruit criteria is relaxed.
How would the code for this look like? As I said, if it's only two criteria, it's relatively straightforward but in reality, I have many more criteria and the code becomes very long.
Thanks