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