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/

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).FormulaR1C1 = _
"=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" <the_stan_brown@fastmail.fm> wrote in message
news:MPG.1e68607fc7cdee4098a0fb@news.individual.net...
> 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/

Fri, 24 Feb 2006 10:22:23 -0000 from Norman Jones
<normanjones@whereforartthou.com>:
> 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/

Fri, 24 Feb 2006 10:22:23 -0000 from Norman Jones
<normanjones@whereforartthou.com>:
> > 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/