Sorting Question #3

Hi.

When I sort a selection by a column that contains blanks the rows with
blanks always sort to the end regardless whether I set ascending or
descending. Is there anyway to reverse this?

   Selection.Sort Key1:=Range("B11"), Order1:=xlAscending, Key2:=Range("M11"
_
        ), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
        :=xlSortTextAsNumbers

Column B contains a either 'X' or ''
Column M contains a number

and I want the rows with an 'X' at the bottom of the list.

TIA Steve

PS Using Office 2003 pro


0
3/31/2005 11:47:37 AM
excel 39879 articles. 2 followers. Follow

3 Replies
395 Views

Similar Articles

[PageSpeed] 53

Maybe you could put something in those blank cells, then sort, then remove the
blanks.

Record a macro when you do your Edit|replaces and you can add them before and
after your sort.

(Use a unique value--so you don't change real data!)

Alternatively, you could sort, then move those rows to where you want them.

runningdog wrote:
> 
> Hi.
> 
> When I sort a selection by a column that contains blanks the rows with
> blanks always sort to the end regardless whether I set ascending or
> descending. Is there anyway to reverse this?
> 
>    Selection.Sort Key1:=Range("B11"), Order1:=xlAscending, Key2:=Range("M11"
> _
>         ), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
> _
>         False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
> DataOption2 _
>         :=xlSortTextAsNumbers
> 
> Column B contains a either 'X' or ''
> Column M contains a number
> 
> and I want the rows with an 'X' at the bottom of the list.
> 
> TIA Steve
> 
> PS Using Office 2003 pro

-- 

Dave Peterson
0
ec357201 (5290)
3/31/2005 12:19:08 PM
You could use a helper column, insert this formula and copy down

="aaaa"&A1

then highlight both columns and sort on the helper column

Vaya con Dios,
Chuck, CABGx3


"runningdog" <runningdog@reply.to.newsgroup> wrote in message
news:#OUqXeeNFHA.3984@TK2MSFTNGP12.phx.gbl...
> Hi.
>
> When I sort a selection by a column that contains blanks the rows with
> blanks always sort to the end regardless whether I set ascending or
> descending. Is there anyway to reverse this?
>
>    Selection.Sort Key1:=Range("B11"), Order1:=xlAscending,
Key2:=Range("M11"
> _
>         ), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:=
> _
>         False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
> DataOption2 _
>         :=xlSortTextAsNumbers
>
> Column B contains a either 'X' or ''
> Column M contains a number
>
> and I want the rows with an 'X' at the bottom of the list.
>
> TIA Steve
>
> PS Using Office 2003 pro
>
>


0
croberts (1377)
3/31/2005 12:49:42 PM
Thanks I hid the helper column and sort on it works good.

"CLR" <croberts@tampabay.rr.com> wrote in message
news:#vHNg#eNFHA.1396@TK2MSFTNGP10.phx.gbl...
> You could use a helper column, insert this formula and copy down
>
> ="aaaa"&A1
>
> then highlight both columns and sort on the helper column
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "runningdog" <runningdog@reply.to.newsgroup> wrote in message
> news:#OUqXeeNFHA.3984@TK2MSFTNGP12.phx.gbl...
> > Hi.
> >
> > When I sort a selection by a column that contains blanks the rows with
> > blanks always sort to the end regardless whether I set ascending or
> > descending. Is there anyway to reverse this?
> >
> >    Selection.Sort Key1:=Range("B11"), Order1:=xlAscending,
> Key2:=Range("M11"
> > _
> >         ), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
> MatchCase:=
> > _
> >         False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
> > DataOption2 _
> >         :=xlSortTextAsNumbers
> >
> > Column B contains a either 'X' or ''
> > Column M contains a number
> >
> > and I want the rows with an 'X' at the bottom of the list.
> >
> > TIA Steve
> >
> > PS Using Office 2003 pro
> >
> >
>
>


0
4/1/2005 12:17:13 AM
Reply:

Similar Artilces:

POP3 Question #3
We have SBS 2003 and ISA 2004, with Outlook 2003 running on the workstations. We are using Exchange for corporate email, but each user also has several different pop3 email addresses at different ISP's we want to send and receive with (Old email address, Boss's personal email, etc). Right now I am using Outlook to add these additional pop3 accounts for the users, which is working fine. Except that I would like to take advantage of Exchange's spam filtering capabilities and centralized administration of the accounts. Should I be using the POP3 Connector of Exchange i...

SP3 question
I'm running WinXP Pro SP2 on a Dell Dimension 3 GHz machine with 2 Gig of RAM. I have only heard bad things from people I know who tried to install SP3 and on newsgroups. It seems that one of three things will happen. 1. Everything will go well and it will work OK. (Seldom) 2. It will seem to go well for some period of time and then something happens and your computer gets screwed up. 3. It will not install correctly and your computer gets screwed up. With the last two, a clean re-install of WinXP is required to get back running. With my luck with updates, one...

question on the "spin" control
Hello all, I am somewhat new to VC++/MFC. In one of my programs there is a spin control gadget with an associated "buddy" window which displays integers from a specified range (the usual behavior). What I want to do is change the format of that display - if the range is 0-100 say I would like to display 0.0-10.0 incremented by 0.1, for example. Now I can access the buddy window's text through its methods Get/SetWindowText, but the question is at what point to do it? If I attempt the access in either of the events EN_CHANGE or EN_UPDATE (of the buddy window), an acception get...

Macro question
Whenever I try to run my macro, nothing happens. When I try to change the security settings through the Tools > Security option, nothing happens. I can do it at home but not at work. Is there a specific reason? I'm the administrator of the computer and I am not connected to any domain. Please help! -- Nocturnal We see this sometimes when another application starts Outlook before you = do and VBA never quite loads properly. Try shutting down Outlook and = using Task Manager (Ctrl+Alt+Del) to make sure it's really, really shut = down before you restart it. You may also w...

bar chart #3
I am trying to make a bar chart. My problem is that excell keeps combining info from 2 different projects and inserts both of them into one bar chart. How do I fix this problem? I assume that you want to see the data as two series in one chart? Put your different projects into different rows, or preferably different columns, select them both, and the Chart wizard should split them into two different series. If that doesn't work, select one series, insert the chart, then go to source data, & add the second series. -- David Biddulph "Ann" <Ann@discussions.micro...

Basic Question
Hi all, I have a table called Companytbl with 2 fields: CompanyID (autonumber), CompanyName(text) On the form ( which related to a different table)I have company combobox. What exactly is the setup so on the combobox it will show the companyName and it will store in the table theCompanyID? TIA, Tom In news:%23Z%23x%23Q9JIHA.5928@TK2MSFTNGP05.phx.gbl, Tom <partner1973@yahoo.com> wrote: > Hi all, > > I have a table called Companytbl with 2 fields: CompanyID > (autonumber), CompanyName(text) > > On the form ( which related to a different table)I have company &...

How to Merge Date from 3 columns to 1
I have a spreadsheet where the date is spread over 3 columns I,J & K and I wish to merge them to Column B in the format dd-mmm-yyyy as shown below. B I J K 02-Feb-2003 02 Feb 2003 27-Mar-1999 27 Mar 1999 01-Apr-1978 Apr 1978 01-Jan-1998 1998 14-Jun-2002 14 Jun 2002 Where there is a blank for the day in 'Column I' I need it to copy as 01 and where there is a blank for the month in 'Column J' I need it to copy as Jan. Where all 3 column...

Sorting
Hi, is it possible to use variable for sorting? Something like: declare @sort varchar(10) SET @sort='column1 DESC' SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table Is dynamic SQL only option? Thank you, Simon On 15/04/2010 11:28, simon wrote: > Hi, > > is it possible to use variable for sorting? > > Something like: > > declare @sort varchar(10) > SET @sort='column1 DESC' > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > Is dynamic SQL only option? You can use CASE to he...

How to sort account list?
I'm using MS Money 2004 Small Business. In the account list, my accounts are catagorized by and in this order: - bank accounts - credit accounts - investment accounts - loans and liabilities - asset accounts TOTAL BALANCE In the above view, I cannot easily determine totals for assets and liabilities. The following view does this. - bank accounts - investment accounts - asset accounts SUBTOTAL -loans and liabilities SUBTOTAL TOTAL BALANCE How do I get the above view? Thanks, Brett I don't use Small Business. But you can run a Net Worth report to get total assets and ...

VBA Newbie listbox question
How do i add stuff to a second column in vba.. i feel really stupid but i've tried endlessly and searched the internet with no joy... Also how can i use info from a particular column.. eg. Surname/name are 2 column headings (assuming i can get stuff in the 2nd colum) i wanna take data from 'surname' without takin the 'name' data with it... I know this is really simple basic stuff so can someone put me out of my misery? --- Message posted from http://www.ExcelForum.com/ -bump-..... -- Message posted from http://www.ExcelForum.com Devitt Try these Populate Multi...

Where's the Sort by New Message option?
Not sure when the layout of the newsgroups changed but I can't seem to find the ability to sort by new message in a thread instead of sorting by just the original thread date. That was hugely helpful as I could follow along and see what threads were still active. If you're gonna hang around in the newsgroups (any newsgroups), you may want to start using a newsreader. Microsoft Outlook Express can do it for you. Saved from a previous post: You may want to connect to the ms newsserver directly: If you have Outlook Express installed, try clicking on these links (or copy and paste i...

Row names will not move with sort
Each row in my summary table in Sheet1 is hyperlinked to a corresponding table in Sheet2 with additional details. I need to move the rows in my summary table (Sheet1) and preserve the links to and from the details tables (Sheet2). I found that I can move rows in Sheet1 with cut & insert and the hyperlinks are maintained. I can travel from a row in summary table (Sheet1) to details table (Sheet2) and back, even after the corresponding row (Sheet1) was moved. But if I sort the rows in Sheet1 the links from Sheet2 back to corresponding rows in Sheet1 lead to presorted locations. Apparen...

Probably very easy question conerning auto-changing dates
I've got a list of dates in column B. Row 1 is labeled "date", Row 2 starts my date data (May-04). I want a list in column B of the preceding 23 months, so columb B ends with Jun-02. Obviously, I can manually input this information. What I can't figure out is how if I go and change the first date (say, to Jun-04) it will auto-change my list of dates accordingly (so my list would now end in Jul-02). NOTE: I know how to create a handle-fill, but it won't update any changes I make to the dates, nor can I get it to go backwards, chronologically. Thanks, Jacob jfarino@ameris...

Identities #3
Is it possible to have multiple identities (accounts) in Outlook 2000 as can be done in Outlook Express? If so, how do you switch identities so that more that one person can use the same system? You can if Outlook 2000 is used in Corporate/Workgroup mode (CW mode). You can then configure profiles in Control Panel-> Mail-> button Show Profiles... -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "John" <anonymous@discussions.microsoft.com> wrote in messag...

Business Portal Question
I'm trying to install BP4.0 on a web server. The SQL environment is setup as a cluster/farm. (GPV10). During the BP requirements pre-check - I get an error message that the Number of Processors requirements failed. I ignore that check and tried the install anyways (always the optimistic person I am) and I get the error message: "Your Operating system does not meet the requirements for Business Portal. The supported systems are Windows Server 2003 (standard, enterprise, and web Edition) and small business server 2003. The web server is a Windows Server 2008 (32 b...

MSChart Question
Can an inset MSChart object have 2 different styles of charts - like a target line on a stacked bar chart? Would this require 2 charts, one on top of the other like layered graphics? Does anyone have experience with overlaying charts and the inherent pitfalls. Or, a different solution idea. Thanks! It certainly can. You do, of course, need at least two sets of y-axis data in the recordsource for the chart itself. Then you can select one of the pre-defined mixed chart types from the Custom Types tab in the Chart Type .... dialog (available either from the Chart menu or via the right-clic...

Exchange System Manager Error #3
I just installed a new certificate on my Enterprise exchange server. I had been using a Small business server as a front end server and recently ran the transition pack to convert to a full Windows 2003 server. I am no longer using the exchange that was on the small business server and have disabled all of the services until I get a chance to remove it. When I try to access public folders from Exchange System Manager, I get the following error: the SSL certificate server name is incorrect ID no: c103b404 Exchange System Manager I am not sure what is causing this. All of the public ...

sorts not sorting
OK- Thanks to everyones help I now i have a list i can work with... but there are a few problems.... the zips are all 5 digit and now i have to sort them... the problem is, THEY WONT SORT CORRECTLY!!!!!!! The numbers are not in order... some are but a lot are not... is there anyway to fix this???? i need to sort them by zip code but cant seem to get them to do it correctly.. any ideas??? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You are going to be far better of...

Mailbox limits #3
We have configured mailbox limits on our mail stores using system policies but they do not appear to be working mailboxes are able to exceed these limits and I can not see a pattern to it. I have search the log files and i see no information on the mailbox management re limits beging exceed i have logging turned on. Any Ideas as to validate the configuration On Mon, 13 Dec 2004 11:37:11 -0800, "Nollaig" <Nollaig@discussions.microsoft.com> wrote: >We have configured mailbox limits on our mail stores using system policies >but they do not appear to be working ma...

HELP: Final Question About Hackers Stealing Files Via Wireless Net
Hello, my question is to verify if these statements are true, I wanted to email these guys personally but I understand it's better to share this with everyone. The questions concern if hackers can break into my Vista computer and access the hard drive's files, if my laptop is wirelessly connected to an unsecured router (meaning they connect to the unsecured router, and connect to my laptop that's wirelessly connected to the router). Also, I have not shared any folders (so was wondering if with cracking admin access they could get around it). The first ...

import- number- sort?
I imported this data into a spread sheet- I tried to format it to number, but when I sort it- I get : 37.08 4.33 4.79 4.8 42.56 5.94 Whats up with this? Thanks Brent Hi Brent, Install the TrimAll macro, select the column and invoke the macro from Alt+F8 http://www.mvps.org/dmcritchie/excel/join.htm#trimall Directions to install and use a macro at http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro I expect that you have spaces or CHAR(128) "Required Blank" characters in you data you can check is =LEN(A1) --- HTH, David...

Exch 5.5 to 2003 upgrade question
Exch 5.5 to 2003 upgrade question We are running one (1) Exchange 5.5 server on a Windows NT 4.0 box and would like to migrate over to a new box running Windows 2003 and Exchange 2003. I understand that I must first upgrade my NT 4.0 domain to Active Directory and DNS (so let's just say that I have done that). I will install Windows 2003 and Exchange 2003 on a NEW box and join the Exchange 5.5 site. After that I will do a move mailbox to start moving people over to the new mail server. Here is my question: Let's say that my 5.5 server has 300 mailboxes that I have to move...

sorting macro #3
I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a time but I know there must be an easier way. Can ANYONE HELP? C3:J3 I need these cells sorted and then down to C1532:J1532 I did create a macro to sort after I selected them but still one at a time. Does it have to be a macro? I can edit a macro but don't really know how to program them. Yes, it has to be a macro. The following should do it. This macro loops through all the entries in Column C, and in each row it sorts the values in Columns C:J. HTH Otto Sub SortRows() Dim rColC As Range Dim i As...

Can we sort a form and also be able to enter new records.
Hi, It is possible to sort the data on a form and also be able to enter new and edit the existing records in it. Cheers! On Tue, 15 Jan 2008 15:41:16 -0800 (PST), Rex wrote: > Hi, > > It is possible to sort the data on a form and also be able to enter > new and edit the existing records in it. > > Cheers! I assume you mean "Is it". The form sort order has nothing to do with editing or adding new records, so the answer is yes. If you cannot edit or add new records, make sure the form's record source, if it is a query, is updateable. Some queries are not. O...

Sorting checking register
How do i sort my check register in Money 2004 Deluxe so that uncleared transactions appear below cleared transactions? In Quicken i could click on the column to provide that sort. In microsoft.public.money, Dean wrote: >How do i sort my check register in Money 2004 Deluxe so >that uncleared transactions appear below cleared >transactions? In Quicken i could click on the column to >provide that sort. You don't. Available choices are chosen by clicking View at the top of the register. ...