Results 1 to 2 of 2

Thread: Unfilter only the active column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2016
    Location
    Connecticut, USA
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Unfilter only the active column

    Dear Forum members

    Looking for some help here, I am relatively new to VBA so this might be elementary for some of you, although I do not know how to do it

    I have a large Excel spreadsheet with about 6000+ rows and 25+ columns
    I have filtered all the column headings using Data > Filter

    For analysis, I often filter the spreadsheet on several columns based on search criteria.

    Sometimes I want to UnFilter only one column - the active one that my selected cell is at.

    I know I can do that by clicking the filter drop down for that heading and checking "Select All", but that is several clicks and slows me down

    I was wondering if there is a VBA macro so I can create a button on my Quick Access Toolbar so whenever I click it, the column of the active cell gets unfiltered without disturbing any of the filters on the remaining columns.

    I did a Google search and there are several suggestions in many Excel forums, but they all require a specific column to be called out (Field:6, for example) for unfiltering as part of the code

    I work with several spreadsheets, with similar analysis. Not always I want to unfilter the same column and not always are the spreadsheets the same.

    Basically what I am looking for is a VBA macro that first identifies the column of my active cell, then if that column is filtered, it executes a "Select All" command on that column only. I can then create a button on the QAT and link it to that macro.

    Thanks in advance for support

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Unfilter only the active column

    Hi,

    Try this:

    Code:
    Sub RemoveActiveFilter()
    
    Dim FilterNum As Long
    Dim FilterRange As Range
    
    Set FilterRange = ActiveCell.CurrentRegion
    
    If Not Intersect(ActiveCell, FilterRange) Is Nothing Then
        FilterNum = Intersect(ActiveCell, FilterRange).Column - FilterRange.Column + 1
        FilterRange.AutoFilter Field:=FilterNum
    End If
    
    End Sub
    Last edited by jorismoerings; Oct 14th, 2019 at 03:53 AM.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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