?
Solved

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

Posted on 2012-09-19
8
Medium Priority
?
377 Views
Last Modified: 2012-09-20
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
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "K5:K150000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
        With ActiveWorkbook.ActiveSheet.Sort
            .SetRange Range("A4:U150000")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Range("A5").Select
    Application.ScreenUpdating = True
End Sub
0
Comment
Question by:user2073
  • 4
  • 4
8 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38416441
Hi

Does this help you ?

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

Open in new window


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

Kris
0
 

Author Comment

by:user2073
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

by:krishnakrkc
ID: 38416530
Hi

It works fine here.

it should be a comma.

# 8 line should have been

.Columns(11).Replace -9999999.99, vbNullString

Open in new window


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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:user2073
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.

Your Help is appreciated.
0
 
LVL 18

Expert Comment

by:krishnakrkc
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

by:user2073
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

by:
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
        .Sort .Cells(2, 11), xlDescending, Header:=xlYes
        .Columns(11).Replace ReplaceValue, vbNullString
    End With
    
End Sub

Open in new window


Kris
0
 

Author Closing Comment

by:user2073
ID: 38417186
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

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

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

Join & Ask a Question