Custom filtering

[tiredofit]

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.

    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

 

[RoryA]

You could do something like this

If (Sheet1.Cells(Counter, 1).Value = Sheet1.Cells(2, 1).Value Or Sheet1.Cells(2, 1).Value = "All") And _
           (Sheet1.Cells(Counter, 2).Value = Sheet1.Cells(2, 2).Value Or Sheet1.Cells(2, 2).Value = "All") Then Sheet1.Rows(Counter).Hidden = False

 

[tiredofit]

You could do something like this

If (Sheet1.Cells(Counter, 1).Value = Sheet1.Cells(2, 1).Value Or Sheet1.Cells(2, 1).Value = "All") And _
           (Sheet1.Cells(Counter, 2).Value = Sheet1.Cells(2, 2).Value Or Sheet1.Cells(2, 2).Value = "All") Then Sheet1.Rows(Counter).Hidden = False

Thanks for your speedy reply.

My only worry is given I have a lot more criteria (10), the combinations becomes very long.

Still, as long as it works .....

 

[RoryA]

You'd probably be better off actually using filters.

 

[tiredofit]

You'd probably be better off actually using filters.

I agree but client wants their custom ones.

 

[RoryA]

I mean that you could achieve the result you want using actual filters on the range in question.

 

[tiredofit]

I mean that you could achieve the result you want using actual filters on the range in question.

Thanks, I'll give that a try.