Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reset Advanced Filter (97;SR2)

    I was searching the internet and came upon code to reset the advanced filter but did not pay any attention to it. Well it turns out that I could put it to good use.
    Unfortunately I don't remember which site.

    Any assistance would be appreciated.

    Thanks,
    John

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reset Advanced Filter (97;SR2)

    What is it you need to achieve:

    - just show all data (unapply the filter)

    ActiveSheet.ShowAllData

    - Show all data AND remove the settings of Advanced Filter

    ActiveSheet.ShowAllData
    With ActiveWorkbook
    .Names("'" & ActiveSheet.Name & "'!_Filterdatabase").Delete
    .Names("'" & ActiveSheet.Name & "'!Criteria").Delete
    End With
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Reset Advanced Filter (97;SR2)

    ShowAllData will give an error if the dataset already shows all the data.

    I would suggest using:

    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData

    instead of just the line:
    ActiveSheet.ShowAllData

    Steve

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reset Advanced Filter (97;SR2)

    Correct, of course. Thanks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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