Advanced Filter: Criteria: ?Create & Hold in Code; OR ?Is Worksheet Table Essential

Default300

Default300

Board Regular
Joined
Jul 13, 2009
Messages
83
Previous Question (Interrelated):

This is a follow-on question from another thread, as initial problem is now solved.

I presume it is better to post replies to the new question here rather than there.

More detail on the setuup of this project or task can be found at the Previous Thread...

Thread Title: AdvancedFilter - Criteria Named Range on Different WorkSheet - No Records Returned (except with bizarre MsgBox workaround)

Thread ID: http://www.mrexcel.com/forum/showthread.php?t=403272

Link to Post that branches to here: http://www.mrexcel.com/forum/showpost.php?p=2094394&postcount=13


New Question:

Regarding Advanced Filter:
Must the Criteria Table be Written to and Read from a WorkSheet?
Or can the Criteria be Contructed in and held in Code?

For Constructing the Criteria:
I'm thinking of somehow using the Formulae I used in the the criteria table cells, perhaps even worksheet functions.

For Holding the Criteria in a Matrix (if this is required):
I'm thinking of using a two dimensional array variable.

But maybe this transalation from Excel to VBA is too literal.

I'll submit this now and then fill in more information soon after.
(In case anyone is looking for it... Fingers crossed!)
 
Sort by date Sort by votes
To use Advanced Filter, the Criteria Range has to be a range on a sheet.

You can use named ranges. Both of these codes worked for me.

Code: 
Range("A1:A6").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:="NamedRange", Unique:=False

Range("A1:A6").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("C1:C2"), Unique:=False
 
Upvote 0
The information I wanted to post before I get some sleep for my real job in five hours is about the current Data Table.

It is 3 Columns wide by 6 Rows, + 1 Header Row.

-| A ----- | B ------ | C ----- |
1| CLIENTS | LOCATION | REGULAR |
2| ------- | =AA ---- | ------- |
3| ------- | =AB ---- | ------- |
4| ------- | =AC ---- | ------- |
5| ------- | =AD ---- | ------- |
6| >0 ---- | -------- | ------- |
7| ------- | -------- | =Y ---- |

Cells above wih just dotted lines) are Blank.
The other cells all have Formulae.
From the top, they are as follows:

B2
=IF(AND(namFormCurUserCurSetting_OptFiltersAllActivatedEnable=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetLocationsRegularAll=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetLocationsRegular1=TRUE,namFormCurUserCurSetting_CboFiltersLocationsSelectLocation1<>""),"="&namFormCurUserCurSetting_CboFiltersLocationsSelectLocation1,"=NA()")

B3
=IF(AND(namFormCurUserCurSetting_OptFiltersAllActivatedEnable=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetLocationsRegularAll=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetLocationsRegular2=TRUE,namFormCurUserCurSetting_CboFiltersLocationsSelectLocation2<>""),"="&namFormCurUserCurSetting_CboFiltersLocationsSelectLocation2,"=NA()")

B4
=IF(AND(namFormCurUserCurSetting_OptFiltersAllActivatedEnable=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetLocationsRegularAll=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetLocationsRegular3=TRUE,namFormCurUserCurSetting_CboFiltersLocationsSelectLocation3<>""),"="&namFormCurUserCurSetting_CboFiltersLocationsSelectLocation3,"=NA()")

B5
=IF(AND(namFormCurUserCurSetting_OptFiltersAllActivatedEnable=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetLocationsSpecialAll=TRUE,namFormCurUserCurSetting_CboFiltersLocationsSelectLocation4<>""),"="&namFormCurUserCurSetting_CboFiltersLocationsSelectLocation4,"=NA()")

A6
=IF(AND(namFormCurUserSavSetting_OptFiltersAllActivatedEnable=TRUE,namFormCurUserSavSetting_ChkFiltersSetUnsetClientsActive=TRUE),">0","=NA()")

C7
=IF(AND(namFormCurUserCurSetting_OptFiltersAllActivatedEnable=TRUE,namFormCurUserCurSetting_ChkFiltersSetUnsetClientsRegular=TRUE),"="&"Y","=NA()")

They all refer to various Named Ranges (Prefix "nam") which currently are Worksheet Cells, but could potentially become variables.

These Named Ranges store the current Values of various Controls on a UserForm. They are similar to ControlSource linked cells but they are Not ControlSources, as I have decided to leave the Controls Unbound (see previous thread for why).

The three letters after the underscore indicate the type of Control following a standard naming convention ("chk" =CheckBox, "Opt"=OptionButton and cbo=ComboBox).

Can't type anymore tonight!

Talk tomorrow.

Thanks.
 
Upvote 0
In lieu of a virtual Criteria Range, I have the VB routine:

1) Write the criteria data in a range to the right of the Used Range (leaving a blank column

2) Advanced Filter using that Citeria Range.

3) Delete the Critieria Range columns.
 
Upvote 0
Mike, thanks again for sharing your brain power to assist me.

Shame if what I was hoping to do isn't possible, but not the end of the world.

Aim / Intentions

  • Speed Up Code by avoiding writing to Worksheet unnecessarily
  • Avoid having a different Criteria Table for each Worksheet (though this may also have advantages)
  • Some others I can't remember right now (perhaps to do with the other Controls used in formulae not having to write to Worksheet)

Brainstorm

Theory for Execution of Virtual Criteria Table…

  • Create an Array Variable with same Dimensions as my physical Criteria Table (0 To 6, 0 To 2) if the header should be included.
  • Not sure, but think the DataType should be Strings at this stage, to store values such as "=AB".
  • Store the result of each of my formulae (in Post #3) in the appropriate location within the virtual table (eg "=AB" in (1,2) if header row included)
(From here on, my theory probably breaks down)

(Suggestion 1)

  • Dimension, ie Dim a Range Variable
  • Load the array into the range variable (?with Set statement)
(Suggestion 2)

  • Assign the Array to a Name (since Names can be either Ranges or Constants)

Alternatively...

  • Sticking with Names, skip the array stage and instead define 6 six Names (each to hold the result of just one the formulae), ?ignoring the blank cells.
  • Possibly define another Name to hold the six values together as a group.
  • Use this as Criteria Range (somehow!)

Alternatively (and probably far to slow to be worth it)...

  • Load Data to be filtered into an array.
  • Using Loops, and Find method, search data record by record marking records for inclusion or exclusion.
  • Hide All Rows Marked for Exclusion
End of Brainstorm !!


Hope some genius can cobble together something out of my concept(s), otherwise, will (un)gracefully accept defeat.

Thanks Mike and all other "good Samaritans".
 
Last edited:
Upvote 0
To, Avoid having a different Criteria Table for each Worksheet (though this may also have advantages)
Click to expand...

When running Advanced Filter from the keyboard, the Criteria range does not need to be on the same sheet as the List range. It all depends on which sheet is active when the AdvancedFilter button is clicked.

If you are using Copy to other location, the sheet with the Copy To range should be active.
If Filter in Place, the sheet with the Criteria range should be active when AdvancedFilter is pressed.

Having the ranges on different sheets and attempting AdvancedFilter with the improper sheet active will result in an error message that could be interpreted to mean that all three ranges must be on the same sheet. That interpretation is false.

If using AdvancedFilter with VB code, the Active sheet doen't matter, fully qualifying the various range arguments is sufficient.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
VBA: Advanced Filter Limitations
Replies
3
Views
1K
zero269
Z
B
  • Question Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
2K
Alex Blakenburg
A
M
  • Question Question
VBA Help - Advanced Filtering
Replies
1
Views
934
DanteAmor
DanteAmor
W
  • Question Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
619
Anthony47
A
Z
  • Question Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
448
zero269
Z

Share this page

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