Solved

# How can I sort numbers in a spreadsheet that has a mixture of blank cells.

Posted on 2012-09-19
370 Views
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.

Sub NumberSort()
' Sort By Name Macro

Application.ScreenUpdating = False
Range("A5:U150000").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
"K5:K150000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A4:U150000")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A5").Select
Application.ScreenUpdating = True
End Sub
0
Question by:user2073

LVL 18

Expert Comment

Hi

``````Sub NumberSort()
' Sort By Name Macro

With Range("A4:U150000")
On Error Resume Next
.Columns(11).SpecialCells(-4123, 2).Value = -9999999.99
.Sort .Cells(2, 11), xlDescending, Header:=xlYes
.Columns(11).SpecialCells(-4123, 2).Value = vbNullString
End With

End Sub
``````

I replaced all the blanks in Col K with -9999999.99 and later replaced with null.

Kris
0

Author Comment

The code above displayed a "Compile error: Expected: =" message  on line 7 , highting the comma after ".Sort .Cells(2, 11), " should this be a full stop (.) instead.

Also, if I replace the blank with -9999999.99, would this be seen or is it only in the cell temporary during processing?

Question 2. Can you explain to "SpecialCells(-4123, 2)" I don't understand this piece of code.
0

LVL 18

Expert Comment

Hi

It works fine here.

it should be a comma.

# 8 line should have been

``````.Columns(11).Replace -9999999.99, vbNullString
``````

Q2.

SpecialCells(-4123, 2) will give you a range where text displayed using formula including "".

to get better ideas, record the macro.

select the formula range > hit F5 > Special > check formulas > Select text

Kris
0

Author Comment

I have run the code with the changes you have made.  The code failed and returned on the screen a "Syntax Error" then hightlights Row 7 as before.

Do you have any suggestions.

Thanks for the answer to Question 2 i'll play with it later.

Your Help is appreciated.
0

LVL 18

Expert Comment

Hi

Could you please attach the workbook here? Feel free to remove the sensitive data and put some dummy values.

Kris
0

Author Comment

I have an attached file that only shows the workings. The macro for sorting is part of the file.
Please, I need the solution to with this file because all formatting is control from another source. The formatting & data for column "K"  is shown in the attached worksheet "SampleCode".
To test the sorting for column "K" there is a "Lemon" button at the top of the column. When you press it this will show you my problem if you need to run the macro a second or Third time you'll need to manually sort the worksheet.

THANKS Kris
TestCodesB-EE.xlsm
0

LVL 18

Accepted Solution

Hi

OK. Try this

``````Sub NumberSort()
' Sort By Name Macro

Dim rngTexts    As Range

Const ReplaceValue  As Double = -9999999.99

With Range("A4:U150000")
On Error Resume Next
'works if formulas there
Set rngTexts = .Columns(11).SpecialCells(-4123, 2)
If rngTexts Is Nothing Then
'works on constants
Set rngTexts = .Columns(11).SpecialCells(2, 2)
End If
If Not rngTexts Is Nothing Then rngTexts.Value = ReplaceValue
.Sort .Cells(2, 11), xlDescending, Header:=xlYes
.Columns(11).Replace ReplaceValue, vbNullString
End With

End Sub
``````

Kris
0

Author Closing Comment

You guys are fabulous this code is working perfectly. Thanks for the code.
Your assistence is very much appreciated it is helping to learn more about the code.

Keep up the great work.

THANKS
0

## Join & Write a Comment Already a member? Login.

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

#### 732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!