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?

 

[My Aswer Is This]

A Table can be used as a dynamic named range. But do not give your Table and named range the same name.
Show me the script your trying to use.

 

[daveyc18]

I deleted the original named ranged and used the same name for the table ...I'll post the code shortly

 

[daveyc18]

A Table can be used as a dynamic named range. But do not give your Table and named range the same name.
Show me the script your trying to use.

        Range("a1:an" & finalrow).AdvancedFilter Action:=xlFilterInPlace, criteriarange:=ThisWorkbook.Sheets("Maintenance").Range("Filter_All_Regions_Except_SB")

Im thinking I need to modify the "RAnge("Filter..")" part since it's no longer a range and now actually a table???

also, when is a named range actually dynamic? Are they naturally dynamic in a sense when you move the data? e.g., move the named range from A1 to D2. But when you ADD data to the range, it's NOT dyanmic? i think that's what I noticed.

 

[My Aswer Is This]

I'm not able to help you without knowing what your trying to do.

I was just mentioning a table can be used as a dynamic name range.

For example create yourself a Table and give it any name you want.

Now write a small script which might say Range("John").select and run the script and see what happens.

Now add more data to the Range and then run your script again Range("John").select and you will see it now selects the enlarged range.

 

[daveyc18]

That's exactly what I did ...but doesn't seem to work with a named table vs named range ...ill try again

 

[daveyc18]

I'm not able to help you without knowing what your trying to do.

I was just mentioning a table can be used as a dynamic name range.

For example create yourself a Table and give it any name you want.

Now write a small script which might say Range("John").select and run the script and see what happens.

Now add more data to the Range and then run your script again Range("John").select and you will see it now selects the enlarged range.

ok, i just tried with a fresh workbook, making up my own data. advanced filter doesn't work with named tables, only named ranges.

 

[andrex]

you can make a dynamic name range, that would solve the problem of adding data to your list

 

[daveyc18]

Is it possible to make a dynamic range where its more than one column ?

 

[andrex]

something like this should do the work

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

max will control the length of the table, the last digit how many column you have in the dynamic range