Pass variables into Advanced Filter with VBA

[RockandGrohl]

Hello everybody,

I'd like to create an advanced filter that doesn't require a user-input table, instead using the results of checkboxes.


In my dataset there are six columns that each have numbers from 0 upwards, which are our customer booking stats. Originally, I was passing an autofilter in that ended up making these "AND", so if I wanted to see Family Fun and Sports, it would only show me people who have bought a Family Fun AND a Sport product, and not both.

Instead, I want OR and that's where I've discovered the Advanced Filter (can you believe it, like 8 years in Excel now)...

    Range("A1:BC302868").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Sheets("Sheet1").Range("D1:E3"), Unique:=False

This is what the macro-recorder gives me, but it's annoying that I have to type out the criteria on a separate grid and reference it. It's also not user-friendly, considering I have taken great pains to craft this Userform:

So in this example, let's say you put in Family Fun "From 1" and Sports "From 1" (blank in the To box is no upper limit)

I would want the sheet to filter people who have either booked Family Fun or booked Sports, which the advanced filter does beautifully.


However I do want it in VBA format, like this:

Range("A1:BC" & LastrowDF).AutoFilter field:=36, Criteria1:=">=" & FamilyFunBookingsFrom, Operator:=xlAnd, Criteria2:="<=" & FamilyFunBookingsTo

Another annoying consideration is that each From and To box is called "UK" or "Sports", so "EventsBookingFrom"

I'm a bit stuck here as I need to somehow translate the Userform input into a big OR OR OR VBA mess. The only way my Lizard Brain can think of to do it is creating a temp sheet, constructing that criteria automatically in a little table output and then pointing the VBA.advanced filter onto that. Ew.

Help appreciated.

 

[RockandGrohl]

Bugger it.

 

[RockandGrohl]

****, how do I pass AND & OR through this?


What if I say I want Family Fun >0 and <100 or Sports >0 and <100?

Doing this didn't work:

Family Fun	Sports
>0
<100
	>0
	<100

 

[RockandGrohl]

Ah! It's like this..

Family Fun	Family Fun	Sports	Sports
>0	<100
		>0	<100