Only add variables to an array if they're not blank

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
Hi all

Have the following code:

Code:
LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row

    ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=5, Criteria1:=">=" & CLng(DateValue(tdate))                      ' Tour date
    ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=12, Criteria1:="="                              ' Ad Week blank
    ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=2, Criteria1:="Active"                          ' Status Active
    ActiveSheet.Range("$A$1:$W$" & LastrowAD).AutoFilter Field:=9, Criteria1:=Array("*" & PU1 & "*", "*" & PU2 & "*", "*" & PU3 & "*"), Operator:=xlOr, Operator:=xlFilterValues

PU1 through 10 are placenames and can be like "Hereford" "Worcester" "Droitwich" "Pershore" etc.

It's looking in a workbook called AD under column I which contains a string of placenames, like this: "Felixstowe, Ipswich, Stowmarket, Bury St Edmunds, Newmarket, Cambridge, St Neots, Sandy, Biggleswade, Letchworth, Stevenage"


What I'm trying to do is assign valid placenames earlier on in the macro to variables PU1 through to PU10

However, my problem is that if a newspaper only has three/10 valid placenames, what variable name do I give PU4-PU10? If I do "", then the filter will look for [ "*" "" "*" ] which will just return any value. Alternatively, if I name the PU4-PU10 "Blank" then it strips out all results.


Basically the names of what I am filtering will change regularly, in addition to the quantity of what I am filtering. Sometimes I'll just be looking for one value to filter on, sometimes 8, and the names will always change.

Thanks!


The only thing I can think of is 10x IF statements which check if the PU is blank or not, if it's not then it adds another valid criteria to the filter, otherwise it skips.



Just to add some extra context, if column I has a list of strings, and one string contains one of the PU's and another string contains another of the PU's, I want them both to show up, so it's very much an "OR" kind of search.
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
A couple of issues there. First, you should only have one Operator parameter. Second, you can only use two criteria if you are using wildcards.
 
Upvote 0
A couple of issues there. First, you should only have one Operator parameter. Second, you can only use two criteria if you are using wildcards.

Ok If I take out xlOr operator, I need help figuring out a way to have a variable number of variables included, any ideas?
 
Upvote 0
I've got around it with a solution from another post years ago - add a helper column with a SUM of COUNTIF and have the wildcards inserted there, then just filter to any values >0 - just as fast as a filter in the grand scheme of things.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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