Greetings XLers

I have a range that I need to sort in VB with code like:

Worksheets("Summary").Range("A4:T59").Sort
key1:=Worksheets("Summary").Range("J4"),
key2:=Worksheets("Summary").Range("C4")

Typically, there may be, say, 30 rows of data at the top of this range. All
30 rows will have a C value, but not all will have a J value.

After the first sort, the rows with no J value get pushed to the bottom of
the range. Instead, I'd like the sort result to look like this:

C J
Jones 12
Newt 15
Zack 15
Barr
Chen
blank row after this

I don't know if this approach is correct, but I'm trying to count the number
of entries in col C (eg 30), then adjust the range to be sorted accordingly
(eg A4:T33). I just can't get the code to work, and I don't know if there
is an easier approach.

TQ in advance

Giselle

I use a helper column of cells with formulas that force blanks to the bottom of
the list:

=if(j2="",999999999,j2)
and then drag down and sort by that.

You could even do that kind of thing in your code (insert a new column U, add
the formula, sort by that column and then delete that inserted U column.)



Giselle wrote:

Greetings XLers

I have a range that I need to sort in VB with code like:

Worksheets("Summary").Range("A4:T59").Sort
key1:=Worksheets("Summary").Range("J4"),
key2:=Worksheets("Summary").Range("C4")

Typically, there may be, say, 30 rows of data at the top of this range. All
30 rows will have a C value, but not all will have a J value.

After the first sort, the rows with no J value get pushed to the bottom of
the range. Instead, I'd like the sort result to look like this:

C J
Jones 12
Newt 15
Zack 15
Barr
Chen
blank row after this

I don't know if this approach is correct, but I'm trying to count the number
of entries in col C (eg 30), then adjust the range to be sorted accordingly
(eg A4:T33). I just can't get the code to work, and I don't know if there
is an easier approach.

TQ in advance

Giselle

--

Dave Peterson

Thanks, Dave works fine