J
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
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