Change Filter default settings

[KG Old Wolf]

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

 

[Ed in Aus]

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]

If yes try this

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

 

[KG Old Wolf]

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

 

[Ed in Aus]

Let me know if you need help assigning this to a hot key i.e. Ctrl + Shift + F, or maybe an extra button to the ribbon... (http://support.microsoft.com/kb/141689)

 

[RogerC]

If yes try this

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]

Hello KG,

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

Thank you.

Radu J.