Turning off AutoFilter via VBA does not seem to work for this file.

[welchs101]

Hi, i am enclosing a file. When i try to turn off auto filters on this file ......nothing happens.

I used the following code to turn off autofilters but it does not work:

Please Login or Register  to view this content.

Any ideas why? This file seems different in some way.......any ideas?

[romperstomper]

It's a Table, which has the autofilter built-in and it's not the same as the worksheet's autofilter.

[welchs101]

ok so how do i remove this autofilter? Do i have to remove/undo the table somehow..........?

[romperstomper]

You can use:

Please Login or Register  to view this content.

if you really want.

[welchs101]

ok...that worked........but i have another question. if this is a table then can i apply normal worksheet autofilter via vba or am i going to have problems?

[welchs101]

ok it does not work.........let me know if you want me to start anotehr thread.

turning off filter per your code does work but for some reason i try to turn to turn autofilters back on via this code and it does not work.

Please Login or Register  to view this content.

i think it has to do with the fact that this is a table...........is there some way to make this just a normal file again? or i guess my code could be wrong but i have used this before and it has worked ok

[romperstomper]

As always, define "does not work".
Since you apply a filter to a range, it should work, though the filter will not be the worksheet's filter, it will be the listobject's filter.

[welchs101]

ok, here is what i am trying to do and it is not working.

I apply a filter then delete all the rows that are "showing" then unfilter and work with whats remaining.

The code i posted does not work............


so i have two options that i can see
1) continue working with data as as "table"......which have never done before
2) some how remove table instance without affecting underlying data....

any ideas?

[romperstomper]

When I ask you to define 'doesn't work', I mean clarify, not repeat. (i.e. in what way specifically does it not work?) Imagine you are taking your car to the garage - do you just say "it doesn't work - fix it"?

Anyway, you can use:

Please Login or Register  to view this content.

to convert from table to normal range.

[welchs101]

ok, sorry i did not get what you were asking for.

Let me try to explain. Lets suppose i want to filter on "Header5" such that i want to show all the entries in this column which do NOT equal "Pre-Defined". I use the following code to do this

Please Login or Register  to view this content.

I stop the macro just after the above code and while the filters are there.....it is not filtered the way i want it......but i have used this type of code before and it has worked just fine.

does this explain what you were asking?

[welchs101]

also, i looked up how to convet from a table to normal worksheet and they said to do it manually do this

Table Tools => Tools => Convert to Range

Problem is that when i look at the insert menu and then the "Tables" submenu.....the Tables selection is greyed out.......does this mean its not a table?

[welchs101]

ok, found out how to manually convert to range......i had to select region in table then above "Design" ....the words "Table Tools" appeared.....so i clicked on Design and found convert to range.

[romperstomper]

ok, sorry i did not get what you were asking for.

Let me try to explain. Lets suppose i want to filter on "Header5" such that i want to show all the entries in this column which do NOT equal "Pre-Defined". I use the following code to do this

Please Login or Register  to view this content.

I stop the macro just after the above code and while the filters are there.....it is not filtered the way i want it......but i have used this type of code before and it has worked just fine.

does this explain what you were asking?

Need more detail in the "it is not filtered the way i want " part please - how is it filtered?

[welchs101]

ok romperstomper,

as always thanks. i added this code to my vba code as you instructed.....

Please Login or Register  to view this content.

and then applied my normal vba code..........and BAM!!!!!! it worked.

thanks.

[welchs101]

just for completeness,

the way that i wanted the file sorted is as follows:

Lets suppose i want to filter on "Header5" such that i want to show all the entries in this column which do NOT equal "Pre-Defined". In the test file i sent i would have to change the values in header5 column so taht some of teh entries were actually "Pre-Defined". I did that and what happed (while file was a TABLE) is that nothing happened.......ALL the values in the column were shown no filter was applied.

but after applying the code that romperstomper indicated to convert table back to normal worksheet........my vba code for applying the filter worked as expected.