advanced filter will only work for one criterion

daveyc18

Well-known Member
Joined
Feb 11, 2013
Messages
782
Office Version
  1. 365
  2. 2010
for some reason, the advance filter only works for one criterion...when i try to expand the range...it stops working...

e.g im only filtering for

OSP
397

if i add another column

OSP
400


the advanced filter via VBA stops working (just shows blank).....yes, I have modified the named range to capture the additional column...this is driving me nuts
 
In this case, your "test" range:

[TABLE="class: grid, width: 608"]
<tbody>[TR]
[TD="width: 76"]acct[/TD]
[TD="width: 76"]quantity[/TD]
[TD="width: 76"]value[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>=10[/TD]
[TD="align: right"]0[/TD]
[TD]<>8*[/TD]
[TD]<>9*[/TD]
[TD]<>B*[/TD]
[TD]<>X*[/TD]
[TD]<>T*[/TD]
[/TR]
</tbody>[/TABLE]


Note: The data that starts with 8 and 9 should be texts, if they are numbers are not considered.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
In this case, your "test" range:

[TABLE="class: grid, width: 608"]
<tbody>[TR]
[TD="width: 76"]acct[/TD]
[TD="width: 76"]quantity[/TD]
[TD="width: 76"]value[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>=10[/TD]
[TD="align: right"]0[/TD]
[TD]<>8*[/TD]
[TD]<>9*[/TD]
[TD]<>B*[/TD]
[TD]<>X*[/TD]
[TD]<>T*[/TD]
[/TR]
</tbody>[/TABLE]


Note: The data that starts with 8 and 9 should be texts, if they are numbers are not considered.


excellent, thank you so muchj.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.

cant seem to figure this one out



[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD="width: 76"]acct[/TD]
[TD="width: 76"]quantity[/TD]
[TD="width: 76"][/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"][/TD]
[TD="width: 76"][/TD]
[TD="width: 76"][/TD]
[/TR]
[TR]
[TD]217[/TD]
[TD]<>0[/TD]
[TD="align: right"][/TD]
[TD]5*[/TD]
[TD]b*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


that's what i want (please ignore the blank columns)....account 217, anything NOT 0, and any id starting with "5" and "b"...it seems to work, but it doesn't capture everything
 
Upvote 0
cant seem to figure this one out



[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]acct[/TD]
[TD="width: 76"]quantity[/TD]
[TD="width: 76"][/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"]ID[/TD]
[TD="width: 76"][/TD]
[TD="width: 76"][/TD]
[TD="width: 76"][/TD]
[/TR]
[TR]
[TD]217[/TD]
[TD]<>0[/TD]
[TD="align: right"][/TD]
[TD]5*[/TD]
[TD]b*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


that's what i want (please ignore the blank columns)....account 217, anything NOT 0, and any id starting with "5" and "b"...it seems to work, but it doesn't capture everything


also noticed when I try to record the macro using normal filter..it says "too many line cotninuations" after doing "5*"
 
Upvote 0
that's what i want (please ignore the blank columns)....account 217, anything NOT 0, and any id starting with "5" and "b"...it seems to work, but it doesn't capture everything

Try:

[TABLE="class: grid, width: 228"]
<tbody>[TR]
[TD="width: 76"]acct[/TD]
[TD="width: 76"]quantity[/TD]
[TD="width: 76"]ID[/TD]
[/TR]
[TR]
[TD="align: right"]217[/TD]
[TD]<>0[/TD]
[TD]=5*[/TD]
[/TR]
[TR]
[TD="align: right"]217[/TD]
[TD]<>0[/TD]
[TD]=b*[/TD]
[/TR]
</tbody>[/TABLE]


See more examples:
https://support.office.com/en-ie/ar...criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b
 
Upvote 0
Try:

[TABLE="class: grid, width: 228"]
<tbody>[TR]
[TD="width: 76"]acct[/TD]
[TD="width: 76"]quantity[/TD]
[TD="width: 76"]ID[/TD]
[/TR]
[TR]
[TD="align: right"]217[/TD]
[TD]<>0[/TD]
[TD]=5*[/TD]
[/TR]
[TR]
[TD="align: right"]217[/TD]
[TD]<>0[/TD]
[TD]=b*[/TD]
[/TR]
</tbody>[/TABLE]


See more examples:
https://support.office.com/en-ie/ar...criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b



apologies .. my idiotic error..the reason it wasn't working is there were spaces before the "5" and thus wasn't capturing..after doing a trim ...it worked
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

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