removing autofilter sort options

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


0
peterholm (5)
1/13/2006 2:50:15 PM
excel 39879 articles. 2 followers. Follow

4 Replies
462 Views

Similar Articles

[PageSpeed] 14

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.



-- 
Regards

Roger Govier


"peter" <peterholm@yahoo.com> wrote in message 
news:eXhwpCFGGHA.140@TK2MSFTNGP12.phx.gbl...
> 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
>
> 


0
roger5293 (1125)
1/13/2006 3:10:44 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:uNmJIOFGGHA.3896@TK2MSFTNGP15.phx.gbl...
> 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.
>
>
>
> -- 
> Regards
>
> Roger Govier
>
>
> "peter" <peterholm@yahoo.com> wrote in message 
> news:eXhwpCFGGHA.140@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>>
>
> 


0
peterholm (5)
1/13/2006 3:33:53 PM
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

-- 
Regards

Roger Govier


"peter" <peterholm@yahoo.com> wrote in message 
news:OyQHFbFGGHA.3624@TK2MSFTNGP09.phx.gbl...
> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message 
> news:uNmJIOFGGHA.3896@TK2MSFTNGP15.phx.gbl...
>> 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.
>>
>>
>>
>> -- 
>> Regards
>>
>> Roger Govier
>>
>>
>> "peter" <peterholm@yahoo.com> wrote in message 
>> news:eXhwpCFGGHA.140@TK2MSFTNGP12.phx.gbl...
>>> 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
>>>
>>>
>>
>>
>
> 


0
roger5293 (1125)
1/13/2006 3:38:41 PM
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.




"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:O4ffvdFGGHA.1288@TK2MSFTNGP09.phx.gbl...
> 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
>
> -- 
> Regards
>
> Roger Govier
>
>
> "peter" <peterholm@yahoo.com> wrote in message 
> news:OyQHFbFGGHA.3624@TK2MSFTNGP09.phx.gbl...
>> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message 
>> news:uNmJIOFGGHA.3896@TK2MSFTNGP15.phx.gbl...
>>> 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.
>>>
>>>
>>>
>>> -- 
>>> Regards
>>>
>>> Roger Govier
>>>
>>>
>>> "peter" <peterholm@yahoo.com> wrote in message 
>>> news:eXhwpCFGGHA.140@TK2MSFTNGP12.phx.gbl...
>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
peterholm (5)
1/13/2006 4:39:42 PM
Reply:

Similar Artilces:

User removed Anonymous permission from Calendar...how to get it back
A user has deleted the Anonymous permissions from their calendar and I am unable to find a way to get it back. Does anyone know what can be done to add this back to their calendar. TIA In news:Xns9861A9314BAB8bobarcabca@207.46.248.16, Fuzzy Logic <bob@arc.ab.caREMOVETHIS> typed: > A user has deleted the Anonymous permissions from their calendar and > I am unable to find a way to get it back. Does anyone know what can > be done to add this back to their calendar. TIA In that user's Outlook, right-click on Calendar, properties, permissions tab. Add Anonymous, and do whate...

Differences in various auto-fit options (2007)
Does anyone know why these two autofit mechanisms give different results? 1. Right-click in table | AutoFit | AutoFit to Contents. 2. Select entire table | Double-click right border. Right-clicking in a table brings up a list of options. One of them is Auto-Fit. Selecting Auto-Fit, brings up three options: AutoFit to Contents (AFTC) AutoFit to Window (AFTW) Fixed Column Width (FCW) AFTW makes the table as wide as possible. I am not sure how it decides how wide to make each column. AFTC doesn't seem to work all the time. I have a table with 18 columns on a le...

Advanced Search Criteria
Just an FYI, since there doesn't seem to be a solution posted anywhere on the web, at least that I could find. If you want to search using dynamic dates, at least in the case of finding items older than a certain relative date, such as more than 30 days old, I used the following criteria successfully in Outlook 2007: Advanced Find > Advanced (tab) > Field: Sent Condition: On or after Value: 30 days ago ...

Important Word Options to be selected in Word 2007
-- mprnair What about them? -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "mprnair" <mprnair@discussions.microsoft.com> wrote in message news:355F31FF-4AAB-4ED6-B326-88FA301C0EED@microsoft.com... > > -- > mprnair > ...

Remove text from a cell
Hi there I have some data in a spreadsheet which is an output from another program. One column has a series of numbers in it, each with the suffix of "kb" and I want to remove the kb from each cell so that I can sort by number. Is there an easy way to do this without having to edit each cell individually (I have about 600 rows!) thanks Trace Hi one way: Use 'Edit - Replace' the other way: use the formula =--TRIM(SUBSTITUTE(A1,"kb","")) in a helper column >-----Original Message----- >Hi there > >I have some data in a spreadsheet which is ...

Outlook removes in box items upon reply
When I reply to an item (all items) they are removed from my inbox and disapper -- not in deleted items -- I cannot find them. What is up with that and how do I fix it ? "Jeff J" <anonymous@discussions.microsoft.com> wrote in message news:d84701c3ef3a$b3646a70$a501280a@phx.gbl... > When I reply to an item (all items) they are removed from > my inbox and disapper -- not in deleted items -- I cannot > find them. What is up with that and how do I fix it ? View-Current View-All messages. When I look at that, it does nothing. If I cahnge the View to "View Me...

Sorting problems with other worksheets!!!
I have a problem!!! First I have multiple worksheets in a workbook. And in one column that has some names. and the columns next to them has data which is yes/no go/ngo for each person. If I add a person it will update itself in each worksheet. But I need to sort it. So I sort it and it sorts fine in the worksheet I'm working in. But with the other worksheets it sorts it but the data for each person doesn't stay with the person... I hope I explained this problem to where someone can understand. PLEASE HELP!!! It used to happen when a column (in this example the one containing ...

Could not cleanup worktable IAM chains to allow shrink or remove file operation. Please try again when tempdb is idle
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01CB1448.E88B1160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I'm getting this message when trying to clear tempdb DBCC SHRINKDATABASE (tempdb, 10) WITH NO_INFOMSGS=20 Msg 5054, Level 16, State 1, Line 1 Could not cleanup worktable IAM chains to allow shrink or remove file = operation. Please try again when tempdb is idle. Msg 5054, Level 16, State 1, Line 1 Could not cleanup worktable IAM chains to allow shrink or remove file = op...

Sorting Alpha-Numeric Values
I would like to sort data that have aplha numeric values. When I use the sort command, the numbers wind up getting sorted by place values instead of the actual value of the number, kind of like alphabeticaly except with numbers instead of letters. For instance, I would like to these numbers sorted like this: 17A 36B 265F 1492C 1609A Instead, Excel sorts them like this: 1492C 1609A 17A 265F 36B It appears that the suffixed alpha characters alter the way the numbers would normally be sorted. Any ideas how I can sort them more logically/numericaly? Thanks! Larry ...

Option Buttons 06-21-07
If I choose a Control Source I can get the option buttons to work. Can you help? -- Dick Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200706/1 Do you want us to push the button? What is the problem? UpRider "richardlafrance via AccessMonster.com" <u31432@uwe> wrote in message news:740b97593dc91@uwe... > If I choose a Control Source I can get the option buttons to work. Can > you > help? > > -- > Dick > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/ac...

excel sorting 03-28-10
I have a text spreadsheet of 5,000 names and addresses. Column B needs to restricted to only 16 characters in that column. How do I send the records to the bottom of the page (or delete them) that have data that exceeds 16 characters in Column B? You could insert a new column. In that column, in row 2 put the formula =len(B2) and copy it down the column. Then sort the table on then new column ascending. Tom ...

Sorting data with the letter 'E' included
I have a list of department codes that I need to sort. They are in the format number, letter, number, number. All end in zero; for example 2C00, 5D00, 2E10. In most cases the last two numbers are zeros. The problem is with the codes that contain the letter 'E'. I was originally having the problem of Excel seeing all codes containing the letter 'E' as numbers and putting them into scientific notation. I subsequently formatted them as text which fixed the display problem, but now when I sort, all the department codes that contain 'E' are sorting at the...

Exchange Failing when Removing GC from same box
The background: I am trying to install Exchange 2007 but cannot because I have two Windows 2000 DCs with GCs. The two Windows 2000 DCs happen to be on the same boxes as my Exchange 2000 cluster. I have two other Windows 2003 boxes in the same site with all FSMO roles and both are GC. The problem: I'm in a catch 22 because I cannot install Exchange 2007 and when I remove either the GC role from the Windows 2000 boxes or demote them, Exchange fails. The Question: Has anyone come across a situation like this and know of a solution? Thanks In order to install Ex07, you will ne...

How do I remove the pop up message when saving a CSV
I use alot of CSV files and when I save them I get the message "may contain features that are not compatible with csv". Is there a way to turn this off and just save it? Hi If you do the saving with VBA you could add the line application.displayalerts=false -- Regards Frank Kabel Frankfurt, Germany Eric wrote: > I use alot of CSV files and when I save them I get the > message "may contain features that are not compatible > with csv". Is there a way to turn this off and just save > it? I'm just opening them and changing a piece of data and closing t...

Force TRUE in a check box as result from an option button??
I have a set of option buttons and a set of check boxes. One of the check boxes is for a certain value that is similar to one of the option buttons. Essentially, what I want is for that check box to automatically go to a "TRUE" (checked) state when the certain option button is selected. When the other option buttons are selected, the check box will remain in it's normal state which will allow the user to select it or not depending on other variables. But, if the option button is on that certain selection, I would like to make it so that check box MUST remain on TRUE, and ...

The options turn to grey when I open excel files at IE
If I use another PC. It's no problem. I use Office XP. Thank you very much! My first guess is that opening excel files inside might be the problem. But I just tried it (xl2002, MSIE 6.0, and win98), and the Options were ok (except for one--statusbar). So.... What options are you writing about? And if you are opening within MSIE, you may want to stop that behavior and just open in excel. >From a previous post: There's a setting in Windows that you can change. In win98, I can do this: Start Windows Explorer View|Folder Options File Types Tab scroll down to MS Excel Workshee...

Custom auto-sorting...possible?
Is there a way to have excel auto-sort (cutom) by criteria in 3 columns? I think it might be easier to explain by viewing the workbook http://poolgod.zftp.com/predictions.xls. The picture shows how I need it to sort. I found some information on auto-sort in help and also on the net, but I don't know how to incorporate the multiple criteria or if it's even possible. Any ideas? Thanks, Steve -- Pools ------------------------------------------------------------------------ Pools's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35961 View this thread: ht...

How to succesfully sort column of formulas?
I am trying to sort a column of formulas that consist of cel references. Example: Column: Cell 1 holds: =D10 Cell 2 holds: =D11 Cell 3 holds: =D12 Data: D10=1 D11=2 D12=3 When I try to sort the column of formulas, the formulas change cel references to other cells and mess up the data. How can I sort a column of cells of which all of them contain formula and have the cells keep the references to the right place -- Message posted from http://www.ExcelForum.com Hi- If you write your formulae with absolute references rather tha relative references, you can sort them. Formulae should look ...

Dynamic sorting of rows of data.
Hi, I have data in Column B thru V. Row no 1 is header row. Data extends till lets say row number Z where Z keeps on changing depending on the amount of data. For calculating the value of Z, I use the no of entries in column F as the basis ( as it could be possible that cell B59, C 59 has no data but F59 will have data) This Z is calculated within the VB code and designated as the variable "rowcount". Now comes the real ( and difficult part)... I want to sort the above data. I recorded the macro as per my sorting specifications and got the following code: Rows("2:" ...

how do you sort a table by column 1
thank you in advance -- paul Hi Paul I am not too sure where you are in EXCEL or what you are asking but let me give you a simple example in the hope that it will answer your question. 1. Bring up a new (blank) Workbook. 2. In cells A1 to A5 enter:- a1 a2 a3 a4 a5 3. In cells B1 to B5 enter:- b1 b2 b3 b4 b5 4. In cells C1 to C5 enter:- c1 c2 c3 c4 c5 5. To sort by colum A (in EXCEL 2007):- - highlight A1 to C5 (inclusive) - Data / Sort / Sort by Column A / Order Z to A 6. Hit OK. 7. You will now see cells sorted as follows:- a5 b5 c5 a4 b4 c4 a3 b3 c3 a2 b2 c2 a1 b1 c...

Re: How to remove xmlns attribute from XML document (.net)
Here is an updated link: Prevent Namespace Prefixes from Being Copied to the Output http://www.braintrove.com/Xslt/Articles/8/Prevent-Namespace-Prefixes-from= -Being-Copied-to-the-Output --=20 Greg Collins [Microsoft MVP] Visit Brain Trove ( http://www.BrainTrove.com ) ...

Disable Recently Used Menu Options
How do I disable the option that only shows the most recently used men options. I want Windows to to show all menu options whither I hav used it before or not. It is a setting in the control panel but can't seem to find it. I have Windows XP. Thanks in advance. M -- Message posted from http://www.ExcelForum.com Right click on menu bar. Select Customize. Uncheck the "Menus show recently used commands first" option. Stephani -- Message posted from http://www.ExcelForum.com ...

Option to print attachment icon
I have a problem where some of our clients are able to print a mail that prints out the icon if the mail has a attachment. However some people have attachments, but when they print it does not show the attachment icon on the print out. Any ideas ...

Removed Firewall can send emails
I removed the firewall from my computer, some idiotic little "Freedom firewall" that came pre-installed with this system and am able to send emails just fine now, thanks all. In article <ONv$wgLxDHA.2136@TK2MSFTNGP10.phx.gbl>, support@alfii.com says... > I removed the firewall from my computer, some idiotic little "Freedom > firewall" that came pre-installed with this system and am able to send > emails just fine now, thanks all. Firewalls are a necessary defense, now. If the idiotic little firewall couldn't be configured to allow outbound email con...

SORTING question
Hi. I'm using MS XP (Home) and MS Excel 2003, and I'm just learning how to use the program, so please bear with me (what follows below is a little long-winded). I recently imported a very large Access database file into Excel. This file has five columns, and one of them is a KeyID column (numbering 1 to 60,000). I will be sorting this Excel Sheet frequently, but I need to maintain the exact (original) order of the file for reasons I won't go into here. I will sort the Sheet, make some changes, then sort on the KeyID column to return to the original order (which starts a...