Advanced filters

[poiu]

Hello,

Please could you tell me how to setup the criteria for an advanced filter using the selected items from a UserForm?

Thanks,

Poiu

 

[poiu]

I'm thinking along the lines of replacing CriteriaRange:= Range("B8:AR9") with CriteriaRange: Me.lbCust.List(i) ??? but I would need to use multiple list items

Sub FilterButton()

Range("B11:AR3000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("B8:AR9"), Unique:=False

End Sub

 

[pgc01]

Hi Poiu

That is not possible. The CriteriaRange, like the name says, must always be a range. You have to write the criteria in a range in a worksheet and refer to that range if you want to use the Advanced Filter.

 

[poiu]

Thank you pgc!

 

[jim may]

It's hard to imagine (in an Advanced Filter setting to have a Criteria Range to be 43 columns wide (per below).

CriteriaRange:=Range("B8:AR9")

It appears that LBCust is a ListBox, which likely contains hopefully the same data that is in your table.

My Question!!? Couldn't there be a few lines of code in the RED AREA below - to write the ListItems to a Range("Z1:Z???") and then refer to to them as shown later in the code?

Sub FilterButton()
CODE TO CAPTURE AND WRITE LISTITEMS TO RANGE Z1:Z???
Range("B11:AR3000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("Z1:Z???"), Unique:=False

End Sub


Just wondering......

 

[poiu]

Hi Jim,

Yes I think this is possible. It's just that I'm rewriting someone else's code to speed it up so was trying to avoid switching vba's attention between vba and the worksheet and then back to vba (I'm not sure how to express that properly!)

Thanks,

Poiu

 

[pgc01]

Hi Poiu

I see what you mean. The Advanced Filer is such a powerful and flexible tool that it's a pity that you cannot use it directly in vba using for ex. an array for the data and an array for the criteria. Unfortunately it's not possible.

Your options are:

1 - write code for your specific case

2 - use excel, for ex.:

. add a worksheet (or a workbook)
. copy the data to excel
. execute the filter
. get the result
. delete the worksheet (or the workbook)

or the same using a free range in an existing worksheet.


Notice that you have the same problem with the database functions.

 

[poiu]

Hi PGC,

Thanks, sorry I'm a bit of a newbie - when you say "write code for your specific case" how would I do this? (I thought the criteria had to be entered using a range?)

Also when you say "Notice that you have the same problem with the database functions" are you referring to SQL using ADO/Jet?

Many Thanks,

Poiu

 

[pgc01]

when you say "write code for your specific case" how would I do this? (I thought the criteria had to be entered using a range?)

I mean write code that does not use the Advanced Filter but that performs a similar function. An ex., a function might accept an array with the data table, a column index and a criterion value, and would return an array with only the rows where the value in the data table in the specified column satisfies the criterion.

For ex, you have an array vData(1 to 4, 1 to 3) with the values
1,"a",true
2,"b",false
3,"c",true
4,"d",false

You would write a function that you could call like

vResult = FilterCol(vData, 3, False)

that would return an array vResult with only the rows 2 and 4 of the vData array, that are the rows where column 3 is equal to False.

This is, of course, a solution with a very limited scope, not a general solution like the Advanced Filter. You would have to design a solution to solve your specific problem.

Also when you say "Notice that you have the same problem with the database functions" are you referring to SQL using ADO/Jet?

No, I mean the excel database functions, that also use a criteria range, like DSum(), DCount(), DMax(), etc.