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

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
"Otto Moehrbach" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

Thanks Dave. I'll do just that. Otto
"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson