Announcement

Collapse
No announcement yet.

VBA : Detect and remove autofilter?

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA : Detect and remove autofilter?

    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
    Hi Balangan,

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

    Will remove the autofilter if it is active

    Regards,

    Bill

    Comment


    • #3
      I think it's the whole sheet or nothing. Use this line.
      If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter
      Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            Hi Balangan,

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

            Regards,

            Bill

            Comment


            • #7
              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

              Comment


              • #8
                Or if you just want all of the filters set to All
                If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
                Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                Comment


                • #9
                  Hi Bill, thank you very much for your help. I will now try your solutions thanks you Derry also
                  Best,

                  Balangan

                  Comment


                  • #10
                    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?.

                    Comment


                    • #11
                      Re: VBA : Detect and remove autofilter?

                      Hi RedTussock,

                      Welcome to OzGrid.

                      Unfortunately the practice of "hijacking" a thread is not permitted, as per rule 8 of the rules you've agreed to:

                      "Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread"

                      As such I have closed this thread. Post a new thread with a link back to this one if you think it will help provide a solution.

                      Regards,

                      Robert

                      Comment

                      Working...
                      X