advanced filter will only work for one criterion

[daveyc18]

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

 

[DanteAmor]

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.

 

[daveyc18]

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.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[daveyc18]

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

 

[daveyc18]

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*"

 

[DanteAmor]

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

 

[daveyc18]

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

 

[DanteAmor]

Again a pleasure to help you. Nice night