Sort by an expression?
 

Office 2003, Win XP Pro SP2, English-US

In a macro (created by the Record facility) I have

Selection.Sort Key1:=Range("E2"), Order1:=xlAscending _
, Key2:=Range("D2"), Order2:=xlAscending _
, Key3:=Range("F2"), Order3:=xlAscending _
, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom

What column E contains is either a letter M, a letter S, or a blank.
I want the sort to put all rows with the blank first (using keys 2
and 3 as tiebreaker) and then all rows with the nonblank (regardless
whether it's M or S). In other words, in column E I want letters M
and S to rank equally in the sort of column E, but columns D and F
will be sorted by the normal alphabet. If possible, I want to do this
right in the sort statement.

I know I could do this by creating a dummy column J. Every cell of J
could contain =isblank(E2), =isblank(E3), etc. But that's cumbersome,
and it requires me to remember to extend formulas in J whenever I add
a row in the middle of the table, which I do fairly often.

Is there a way to put some expression as a sort key to accomplish
what I want? I spent some time with the help and couldn't find
anything useful.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Sort by an expression?
 

Hi Stan,

I know I could do this by creating a dummy column J. Every cell of J
could contain =isblank(E2), =isblank(E3), etc. But that's cumbersome,
and it requires me to remember to extend formulas in J whenever I add
a row in the middle of the table, which I do fairly often.

Perhaps you could add a sort button and automate the process, e.g.:

'=============
Public Sub Tester()
Dim rng As Range

'Insert helper column
Columns("F").Insert

Set rng = Selection

Set rng = Selection.Resize(, Selection.Columns.Count + 1)

'Insert formula in helper column
Range("F2").Resize(Selection.Rows.Count).FormulaR1 C1 = _
"=ISBLANK(RC[-1])"

rng.Sort Key1:=Range("F2"), Order1:=xlDescending, _
Key2:=Range("D2"), Order2:=xlAscending, _
Key3:=Range("G2"), Order3:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

'Delete helper column
Columns("F").Delete

End Sub
'<<=============

Note that the third sort key is changed from F2 to G2 to allow for the
inserted helper column.

---
Regards,
Norman



"Stan Brown" wrote in message
t...
Office 2003, Win XP Pro SP2, English-US

In a macro (created by the Record facility) I have

Selection.Sort Key1:=Range("E2"), Order1:=xlAscending _
, Key2:=Range("D2"), Order2:=xlAscending _
, Key3:=Range("F2"), Order3:=xlAscending _
, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom

What column E contains is either a letter M, a letter S, or a blank.
I want the sort to put all rows with the blank first (using keys 2
and 3 as tiebreaker) and then all rows with the nonblank (regardless
whether it's M or S). In other words, in column E I want letters M
and S to rank equally in the sort of column E, but columns D and F
will be sorted by the normal alphabet. If possible, I want to do this
right in the sort statement.

I know I could do this by creating a dummy column J. Every cell of J
could contain =isblank(E2), =isblank(E3), etc. But that's cumbersome,
and it requires me to remember to extend formulas in J whenever I add
a row in the middle of the table, which I do fairly often.

Is there a way to put some expression as a sort key to accomplish
what I want? I spent some time with the help and couldn't find
anything useful.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Sort by an expression?
 

Fri, 24 Feb 2006 10:22:23 -0000 from Norman Jones
:
Hi Stan,

I know I could do this by creating a dummy column J. Every cell of J
could contain =isblank(E2), =isblank(E3), etc. But that's cumbersome,
and it requires me to remember to extend formulas in J whenever I add
a row in the middle of the table, which I do fairly often.

Perhaps you could add a sort button and automate the process, e.g.:

That's really quite clever; thanks! The spreadsheet is only a few
hundred rows, so it shouldn't take much time to add and compute that
column on the fly.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Sort by an expression?
 

Fri, 24 Feb 2006 10:22:23 -0000 from Norman Jones
:
I know I could do this by creating a dummy column J. Every cell of J
could contain =isblank(E2), =isblank(E3), etc. But that's cumbersome,
and it requires me to remember to extend formulas in J whenever I add
a row in the middle of the table, which I do fairly often.

Perhaps you could add a sort button and automate the process, e.g.:

Thanks again for your very helpful response. I integrated your code
into my existing macro and it worked first time: no muss, no fuss.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/