Excel sort ranks text above numbers

[JT]

Dear All

I am preparing a macro that sorts a table in descending order based on
a debt value associated with each row. I have a problem in that some
of the rows have "N/A" in the debt value column and excel is ranking
these above the values when I would like them to be placed at the
bottom of the list.

Can anybody help me to find a neat way of sorting the data in
descending order but so that the text entries are at the bottom of the
list?

At present I am using the following code (rngSortrange represents the
table to be sorted and rngSortcolumn represents the column of the
table contining the debt data):

**************************************
rngSortrange.Worksheet.sort.SortFields.Clear
rngSortrange.Worksheet.sort.SortFields.Add Key:=rngSortcolumn _
, SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
With rngSortrange.Worksheet.sort
.SetRange rngSortrange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
****************************************

Thanks

John

 

[Jim Cone]

In the key column, replace N/A with a large numeric constant....
For example: "Const dblPlugNum as Double = 1234567890 "
It should be greater than any value you would ever find in your data.
Do the sort and then replace the constant with N/A.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html

..
..
..

"JT" <[email protected]>
wrote in message
Dear All

I am preparing a macro that sorts a table in descending order based on
a debt value associated with each row. I have a problem in that some
of the rows have "N/A" in the debt value column and excel is ranking
these above the values when I would like them to be placed at the
bottom of the list.

Can anybody help me to find a neat way of sorting the data in
descending order but so that the text entries are at the bottom of the
list?

At present I am using the following code (rngSortrange represents the
table to be sorted and rngSortcolumn represents the column of the
table contining the debt data):

**************************************
rngSortrange.Worksheet.sort.SortFields.Clear
rngSortrange.Worksheet.sort.SortFields.Add Key:=rngSortcolumn _
, SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
With rngSortrange.Worksheet.sort
.SetRange rngSortrange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
****************************************

Thanks

John

 

[JT]

Thanks Jim

Below you will find the code I have used which does the trick. It
runs pretty quickly even in my set of 25000 data points

Thanks

John

****************************
'Before my sorting code:

Dim cell As Range

For Each cell In rngSortcolumn
If cell.Value = "N/A" Then
cell.Value = 1234567890
End If
Next cell

'After my sorting code:
For Each cell In rngSortcolumn
If cell.Value = 1234567890 Then
cell.Value = "N/A"
End If
Next cell
********************************************