Change Filter default settings

KG Old Wolf

Board Regular
Joined
Sep 20, 2009
Messages
65
I use filters a great deal and would prefer the default setting to be set to "Select All" equal to none so that I can just select the particular condition I am researching.

Okay... one extra mouse click but I do this a LOT! (no longer such a minor thing). Writing a macro wouldn't save any time - the Excel "options" doesn't address such a detail level (as far as I can tell).

Anyone got any suggestions other than just live with it?

Thanks,
KG
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Ed in Aus

Well-known Member
Joined
Jul 24, 2007
Messages
829
Ok let me see if I got this right, you want a macro that when clicked or run it will filter all cells by the current column to blanks?
 

Ed in Aus

Well-known Member
Joined
Jul 24, 2007
Messages
829
If yes try this

Code:
Sub Test()
f = ActiveCell.Column
Cells.Select
    Selection.AutoFilter Field:=f, Criteria1:="=", Operator:=xlAnd
End Sub
 

KG Old Wolf

Board Regular
Joined
Sep 20, 2009
Messages
65
Hi Ed -

Welll this is interesting. I wanted to avoid using a macro and instead just have the Filter's default "all" always be deselected (i.e. no selections). Then I would simply have to check the specific value I wanted (saving the mouse click to shut off the default value). BTW, I am using WIndows 7 / Excel 2007.

I felt a macro wouldn't save any time or steps but yours would and so I'll try it.... very helpful to have a different perspective - the cliche "thinking outside the box" is still true!

Thanks,
KG
 

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
533
If yes try this

Code:
Sub Test()
f = ActiveCell.Column
Cells.Select
    Selection.AutoFilter Field:=f, Criteria1:="=", Operator:=xlAnd
End Sub

I'd like to modify this code, but I'm not following exactly how it applies the filter for 'Blanks' (which is what applying the macro results in).

Can this code be modified so the only thing it does is uncheck the default checkmark from 'Select All', without applying a checkmark to any other item in the filter criteria list?
 

RaduJ

New Member
Joined
Apr 13, 2016
Messages
1
Hello KG,

i am facing the same problem, did you solve it?

Thank you.

Radu J.
 

Forum statistics

Threads
1,136,776
Messages
5,677,666
Members
419,711
Latest member
dacrmcvega0

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
Top