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/