Turn off any filter onopen of workbook

Posted on 2010-01-08
Last Modified: 2012-05-08
I am trying to turn off any filter that was left on close of the workbook when I reopen the book.

onopen I have this
   Set OTEws = Worksheets("OTE's")

    'unfilter the sheet
    OTEws.AutoFilterMode = False
    OTEws.FilterMode = False
    OTEws.ShowAllData = True

I keep getting errors.  What is the best way to unfilter the sheet so I can get a total row count.

thanks xperts
Question by:skillilea
    LVL 18

    Accepted Solution


    On Error Resume Next
    OTEws.AutoFilterMode = False

    LVL 8

    Expert Comment

    Try this code:
    Sub xxxx()
    Dim w As Worksheet
    Set w = Application.ActiveSheet
    'For AutoFilter
    If w.AutoFilterMode = True Then w.AutoFilterMode = False
    'For other filter
    If w.FilterMode = True Then w.ShowAllData
    End Sub

    Open in new window


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now