Sorting with null values

[jayc28]

I am working on a truckload bid for my firm and the client wants the truckload bids sorted by the lowest price. That is easy. However, he knows not every carrier is going to bid and he doesn't want the carriers who do not bid showing up first just because their bid is $0.00 and the actual lowest is $1.05.

Now, there are over 100 hundred separate lanes that I need to sort by lowest price so I am using a macro to do that. However, I still need to set in the VBA code (am a beginner at VBA so please go easy on teh jargon) a way for the null values to go to the back even though, technically the null values are the lowest.

I hope I have made this clear and any help I can get would be greatly appreciated.

[Tim Francis-Wright]

On 2002-05-02 10:35, jayc28 wrote:
I am working on a truckload bid for my firm and the client wants the truckload bids sorted by the lowest price. That is easy. However, he knows not every carrier is going to bid and he doesn't want the carriers who do not bid showing up first just because their bid is $0.00 and the actual lowest is $1.05.

Now, there are over 100 hundred separate lanes that I need to sort by lowest price so I am using a macro to do that. However, I still need to set in the VBA code (am a beginner at VBA so please go easy on teh jargon) a way for the null values to go to the back even though, technically the null values are the lowest.

I hope I have made this clear and any help I can get would be greatly appreciated.

If you had a small spreadsheet and weren't
using VBA, you could use something like:
=IF(COUNTIF($C$2:$C$16,">0")0,$C$2:$C$16),E2))

Where C2:C16 was the range to be sorted and
E2:E16 = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}.
This is an array formula (entered with control-shift-enter), so it's (a) ugly
and (b) apt to slow big spreadsheets down.


But there is an easy way around this.
If your data is in C2:C16, insert a column
D, with D2's formula =IF(C2=0,1,0).
Copy this down as required.

Then the following VBA code will put the
nulls at the bottom:
Range("C2:D16").Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

This should do what you need.