software

Greetings XLers

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

Worksheets(quot;Summaryquot;).Range(quot;A4:T59quot;).Sort
key1:=Worksheets(quot;Summaryquot;).Range(quot;J4quot;),
key2:=Worksheets(quot;Summaryquot;).Range(quot;C4quot;)

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=quot;quot;,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:
gt;
gt; Greetings XLers
gt;
gt; I have a range that I need to sort in VB with code like:
gt;
gt; Worksheets(quot;Summaryquot;).Range(quot;A4:T59quot;).Sort
gt; key1:=Worksheets(quot;Summaryquot;).Range(quot;J4quot;),
gt; key2:=Worksheets(quot;Summaryquot;).Range(quot;C4quot;)
gt;
gt; Typically, there may be, say, 30 rows of data at the top of this range. All
gt; 30 rows will have a C value, but not all will have a J value.
gt;
gt; After the first sort, the rows with no J value get pushed to the bottom of
gt; the range. Instead, I'd like the sort result to look like this:
gt;
gt; C J
gt; Jones 12
gt; Newt 15
gt; Zack 15
gt; Barr
gt; Chen
gt; blank row after this
gt;
gt; I don't know if this approach is correct, but I'm trying to count the number
gt; of entries in col C (eg 30), then adjust the range to be sorted accordingly
gt; (eg A4:T33). I just can't get the code to work, and I don't know if there
gt; is an easier approach.
gt;
gt; TQ in advance
gt;
gt; Giselle

--

Dave Peterson

Thanks, Dave works fine