advanced filter criteria vba

[daveyc18]

hi,

right now im using a named range for my advanced filter criteria, but then i tried making the range a TABLE and re-naming the table to the same same (i want to use a table so that it's dynamic if I add more criteria).

however, when I run the macro, the advanced filter doesn't work. does that mean it won't work with named tables and only named ranges?

 

[andrex]

sorry, missed a comma

=OFFSET(Sheet1!$A$1,0,0,MAX(COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$B$B),COUNTA(Sheet1!$C:$C)),3)

 

[daveyc18]

sorry, missed a comma

=OFFSET(Sheet1!$A$1,0,0,MAX(COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$B$B),COUNTA(Sheet1!$C:$C)),3)

for some reason it's not working...excel gives me an error...highlighting the "$B$B"

 

[andrex]

for some reason it's not working...excel gives me an error...highlighting the "$B$B"

my mistake, column is missing. it should be ...$B:$B...

 

[daveyc18]

my mistake, column is missing. it should be ...$B:$B...

my mistake as well...should have looked more carefully...thanks!

 

[My Aswer Is This]

Show us your entire script when you have it completed.

 

[daveyc18]

Show us your entire script when you have it completed.

the main part of my code is this: Range("a1:an" & finalrow).AdvancedFilter Action:=xlFilterInPlace, criteriarange:=ThisWorkbook.Sheets("Maintenance").Range("Filter_All_Regions_Except_SB")



the named range "filter_all_regions_except_SB" is what andrex provided me:

=OFFSET(maintance!$A$1,0,0,MAX(COUNTA(maintenance!$A:$A),COUNTA(Sheet1!$B:$B),COUNTA(maintenance!$C:$C)),3)


which works out perfectly since my named range has 3 columns (A:C)

 

[My Aswer Is This]

Just thought I would like to see the entire code. But since you do not want to share that it's Ok.
I would have liked to have know what you were trying to do in it's entirety.
Take care.

 

[daveyc18]

Just thought I would like to see the entire code. But since you do not want to share that it's Ok.
I would have liked to have know what you were trying to do in it's entirety.
Take care.

it's too long, lol...and im not at work ....it's essentially filtering for certain stuff from the raw data then just copy and pasting to my workbook

 

[daveyc18]

it's too long, lol...and im not at work ....it's essentially filtering for certain stuff from the raw data then just copy and pasting to my workbook

Does advanced filter only work when the criteria is listed horizontally for Excel 2010?

e.g.

a1 = country
a2 = <>canada

b1 = country
b2 = <>US


at home, i used 2016 to test it out, it seemed like it worked fine if it was listed vertically

e.g.

a1 = country
a2 = <>canada
a3 = <>US