Pass variables into Advanced Filter with VBA

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
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)...

VBA Code:
    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:

1617027654201.png


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:

VBA Code:
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
****, 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 FunSports
>0
<100
>0
<100
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top