Solved

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

Posted on 2012-09-19
Medium Priority
377 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
• 4
• 4

LVL 18

Expert Comment

ID: 38416441
Hi

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

With Range("A4:U150000")
On Error Resume Next
.Columns(11).SpecialCells(-4123, 2).Value = -9999999.99
.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

ID: 38416496
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

ID: 38416530
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

ID: 38416568
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.

0

LVL 18

Expert Comment

ID: 38416614
Hi

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

Kris
0

Author Comment

ID: 38416883
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

krishnakrkc earned 2000 total points
ID: 38417120
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
.Columns(11).Replace ReplaceValue, vbNullString
End With

End Sub
``````

Kris
0

Author Closing Comment

ID: 38417186
You guys are fabulous this code is working perfectly. Thanks for the code.

Keep up the great work.

THANKS
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month17 days, 6 hours left to enroll