How can I sort numbers in a spreadsheet that has a mixture of blank cells.
Posted on 2012-09-19
The macro below is a sort macro which has the order as descending. The problem is when I run the "NumberSort" macro the blank cells are placed above the highest number in the list. The list only contains numbers and blank cells I would like to have the highest number at the top of the spreadsheet with all the blanks at the bottom of the spreadsheet. These cells use a formula to calculate the value & if the value is empty the formula returns with """" could this contribute to the problem. Then I used code to copy and paste special to remove the formulae.
Can anyone help by solving this problem, PLEASE.
' Sort By Name Macro
Application.ScreenUpdating = False
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
"K5:K150000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
Application.ScreenUpdating = True