Sort Orientation

[Otto Moehrbach]

Excel 2002, WinXP
I am sorting 27 columns and 156 rows with the following code:
..Range(.Cells(1, 1), .Cells(LastRow, LastCol)).Sort _
Key1:=.Range("C1"), _
Key2:=.Range("E1")

This abbreviated code sorts in ascending order by default, which is what I
want.

The Key1 column, Column C, is all formulas with many of the formulas
returning "blank" cells. My problem is that Excel is placing all of these
"blank" cells at the top after sorting. I want them to be at the bottom. I
have tried numerous variations of the variable parameters found in the long
Sort command to no avail.
Question: How do I code the Sort command to produce a sort in ascending
order, top to bottom, with the "blank" cells at the bottom?
I think I can do it with a Key1 helper column (Column #28) of =Len(Cx) in
descending order with Key2 (C) and Key3 (E) in ascending order, but is there
a better way? Thanks for your help. Otto

 

[Otto Moehrbach]

That last idea, the =Len(Cx) isn't a good idea because it will mess up the
sort. I would have to modify that formula to produce either a 1 or a 0.
Otto

 

[Dave Peterson]

I'm not sure if it's better, but I think I'd just use a helper column that
evaluated to A or Z.

=if(c1="","Z","A")
fill that column.

and use that as a first key. Sort the data and then delete that helper column.

 

[Otto Moehrbach]

Thanks Dave. I'll do just that. Otto

I'm not sure if it's better, but I think I'd just use a helper column that
evaluated to A or Z.

=if(c1="","Z","A")
fill that column.

and use that as a first key. Sort the data and then delete that helper column.