asked on

Turn of auto filter in MS Excel 2007

I have a workbook where the first worksheet is used as the database.
The other worksheets all have queries that return results from the "database" worksheet.
The query does filter the data so not all the records go into each worksheet.

The workbook is only used for 5 months of the year, during that time I upgraded it to MS 2007 from MS 2003.

The issue I'm experiencing is that MS 2007 is applying an auto-filter to the worksheet so the first row imported has the dropdowns to filter the columns. The filters are preventing other code from running that perform sort and formatting functions.

How do I turn off the auto-filter?

Thank you,
Steph M

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tracy

This will remove the AutoFilter from all the sheets in the ActiveWorkbook.

Sub RemoveAutoFilter

    Dim wkSht As Worksheet
    
    For Each wkSht In ActiveWorkbook.Worksheets
        With wkSht
            If .ProtectContents = True Then .Protect UserInterfaceOnly:=True
                If .AutoFilterMode Then
                    .AutoFilterMode = False
                Else
                End If
        End With
    Next wkSht

End Sub

Open in new window

Patrick Matthews

All of a sudden I'm starting to feel like ture :)

Tracy

who's ture?

Patrick Matthews

https://www.experts-exchange.com/M_313577.htmlWhen I joined EE, he was way up near the top of the Excel leaderboard. He's been inactive for a long time, so now he's down to #25.He was an MVP for a long time; not sure if he is still in the program.Anyway, when someone else would come up with these complex, convoluted solutions, he would come back with a tidy 5-line procedure, or a simple trick in the UI to do the same thing. Then, when his suggestion gets passed over, he would post something like "Why does no one like my simple solutions?":)

Tracy

Oh, I see.

I was going to post what you had too, actually I was going to use the row instead of a cell (Rows("1:1").AutoFilter), but I decided not to, because if there is no filter there already, it will add one. Plus, we all know what happens when a worksheet is protected. So I beefed mine up a little.

Perhaps more than what was requested, but hopefully useful for others when they stumble upon it.

:-)

Patrick Matthews

Touche :)

Steph_M

ASKER

Sorry for the delayed response, I was out of town without internet connection.

I'll try both solutions but will most likely go with the one liner!

I used to have a co-worker that I could go for help and he was just like the ture that you mentioned above. It was amazing to see his work and how efficient he was this stuff.

Steph

Steph_M

ASKER

That did not solve the problem. I'll cleanse the document and post it so you can see what it is doing.

Steph_M

ASKER

Since the filter is causing the problem, and this turns off the filter, I'm accepting this as the solution. The odd thing is my sort is still not working in my production workbook, but when I made a cleansed sample to post, it worked fine. I'm going to try to figure out why the sample one works and not production and if I can't figure out, I'll post it as a new question.

Thank you for your help.

SM