Sorting with null values

jayc28

New Member
Joined
May 1, 2002
Messages
1
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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")<E2,0,SMALL(IF($C$2:$C$16<>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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top