Unfilter only the active column

GuruPepsi

New Member
Joined
May 8, 2016
Messages
4
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi,

Try this:

Code:
<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</code>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top