Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Clear All Advanced Filters (Excel 97 Upwards)

    Hi

    I have a spreadsheet with 174 worksheets, each one set up for using Advanvce Filtering, I also have an Index sheet with cells linked to the totals in worksheets.
    Is it possible to have a macro assigned to a butoon in the Index sheet, that will set all the sheets in the workbook to Showall.
    If so I would like some help with macro!
    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear All Advanced Filters (Excel 97 Upwards)

    Hi Braddy

    I haven't used Advanced Filters recently, but the following will work for AutoFilters:
    <pre>Sub ClearAllWorkbookFilters()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    If ws.FilterMode = True Then ws.ShowAllData
    Next 'ws

    End Sub
    </pre>


    I'm presuming that the .FilterMode property applies the same way for Advanced Filters.

    Alan

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Clear All Advanced Filters (Excel 97 Upwards)

    Hi Alan

    Thanks for the reply, I will give it a try and let you know.

    Thanks again

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Clear All Advanced Filters (Excel 97 Upwards)

    Hi

    Said I would let you know. It worked like a dream.

    Thanks very much

    Braddy

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    If you are a fool at forty, you will always be a fool

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear All Advanced Filters (Excel 97 Upwards)

    Glad to hear it did the job Braddy.

    One thing I forgot to mention is that I (deliberately) used the ThisWorkbook object for this code, rather than referring to the ActiveWorkbook. Your requirements indicated that you wouldn't want this macro firing on some other "random" workbook that might also happen to be open (and active). Just thought I'd qualify its usage in this context.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •