removing autofilter sort options

[peter]

Excel 2003.
When applying autofilter to the column, autofilter dropdown list first
entries are sort ascending and sort descending.
With earlier Excel versions 1st entry used to be All and I used home key to
turn filter off (to select all).
It was handy especially when there was a long autofilter list to scroll up.
Can I remove these obsolete entries from autofilter drop down list?
(Obsolete to me, as I haven't even figured out, why these are there. And
more, when I had tried this sorting option several times I noticed that it
had badly messed up the whole worksheet, where normally only multiple column
sort is acceptable and I was naive to belive that it only for sorting drop
down list entries!)

marti

 

[Roger Govier]

Hi Peter

I don't think you can get rid of those entries on the Autofilter
dropdown, and I agree they are not really necessary.
I have added a button to my Toolbar, Show All, to switch off filtering.
View>Toolbars>Customise>Data and drag the Show All button to the
toolbar.
With the Customise dialogue box still open, if you right click on the
button you have just dragged to the toolbar, you can use Change Button
image to select an icon of your choice.

Of course, if you have made multiple selections on different column
dropdowns, this will not suit as it removes all filters.
You could, instead, go down to Macros in the Customise dialogue and drag
a blank button to the to the toolbar.
Right click the button and choose Add Macro and attach the following to
it.

Sub removecolumnfilter()

Selection.AutoFilter Field:=ActiveCell.Column

End Sub

This assumes, that your cursor is in the column where the filter is to
be cancelled when you click on it.

 

[peter]

Thanks Roger.
I'll do, as you suggested.
I feel much better now, knowing that I'm not just a fool.

By the way, while being naive and trying to sort with these butttons -
result was a total fiasco.
Now the worksheet is ordered (sorted?) in an uknown manner. Luckily I saved
lately and can restore lost data.
When I compared saved version and the "new one" - then in date column are
now some general numbers instead of dates and some dates are now from 1909
instead of 2001 ...

There are very dangerous selections, in this drop down menu!
What kind of sort these are expeceted to be???

marti

 

[Roger Govier]

Hi Peter

It sounds as though your date column is just formatted General.
Mark the column>Format>Cells>Date and choose a format you like or
Format>Cells>Number>Custom > and again choose what you want e.g dd mmm
yyyy

 

[peter]

Thanks.
No, it's not the formatting, I already checked it. There are general numbers
which doesn't make any sense when I convert these into date format. It looks
like partially this option has sorted the single column ("client" column)
and left other colums unsorted. As I tried later to fix it from date column,
the worksheet came even more messed up. And then - there was only one level
of undo abvailable.

But as the whole worksheet is so messed up it's easier to take the worksheet
previous version.
I guess these numbers in date column now, are from other columns.