Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: VBA : Detect and remove autofilter?

  1. #1
    Join Date
    8th May 2003
    Location
    the Med Sea
    Posts
    122
    In a given worksheet, is it possible to create a macro that detects the presence of any active autofilter (in a range of columns, for example from column1 to 10) and remove them?

    thanks.
    Best,

    Balangan

  2. #2
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462
    Hi Balangan,

    Sub RemoveFilter()
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    End Sub

    Will remove the autofilter if it is active

    Regards,

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,386
    I think it's the whole sheet or nothing. Use this line.
    If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th May 2003
    Location
    the Med Sea
    Posts
    122
    sorry i think i explained myself badly. I would like to remove only the value of any autofilter, not the existance of the autofilter itself.
    If, for example, autofilter is active and column "B" contains names and has an active filter on name "Mario", i would like the macro to remove it.
    thanks guys
    Best,

    Balangan

  5. #5
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462
    Hi Balangan,

    Sub removenames()
    If ActiveSheet.AutoFilterMode = True Then
    Sheet1.Range("B2:B20").SpecialCells(xlCellTypeVisible).ClearContents
    End If
    End Sub

    I think this is maybe what you are after.

    Regards,

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462
    Hi Balangan,

    On reading your post again, my last post is probably not much help to you.

    Regards,

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462
    Hi Balangan,

    If you mean to delete all of the information for the filtered range, then change the line to:

    Sheet1.range("A2:J1000").SpecialCells(xlCellTypeVisible).ClearContents

    If you have more than 1000 rows, then change the number. This will clear all of the filtered records in the columns A to J

    Regards,

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,386
    Or if you just want all of the filters set to All
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th May 2003
    Location
    the Med Sea
    Posts
    122
    Hi Bill, thank you very much for your help. I will now try your solutions thanks you Derry also
    Best,

    Balangan

  10. #10
    Join Date
    17th May 2011
    Posts
    1

    Re: VBA : Detect and remove autofilter?

    Just out of Interest ... if the autofilter is over a large number of rows releasing the autofilter can take more than a few seconds of processing time ... yet if you select the drop down tab of the column being filtered it is almost instant .... my question is How come?.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 4 users browsing this thread. (0 members and 4 guests)

Possible Answers

  1. Remove Standard Autofilter Drop Down Choice
    By Robert Phillips in forum EXCEL HELP
    Replies: 3
    Last Post: February 12th, 2008, 02:33
  2. Remove Autofilter From Multiple Worksheets
    By Woddle in forum EXCEL HELP
    Replies: 3
    Last Post: April 24th, 2007, 19:58
  3. Detect AutoFilter off or On
    By boisonbeauty in forum EXCEL HELP
    Replies: 1
    Last Post: April 4th, 2005, 14:18
  4. How to detect and disable AutoFilter?
    By s_a_o_l in forum EXCEL HELP
    Replies: 3
    Last Post: August 24th, 2004, 01:55

Bookmarks

Posting Permissions

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