Sort Blank cells to bottom in a descending sort in VBA

sjedi

New Member
Joined
Dec 8, 2019
Messages
11
Office Version
2016
Platform
Windows
I am sorting the following columns independently and in descending order, i.e. the largest value in each column will always be in the top row. However, I have several blank cells in the table. These are not actually blank, but rather the formula e.g. IFERROR(H2/I2,"") gives a blank answer. I wish to sort the blank cells at the bottom of each column when performing the descending sort. How can I amend the following VBA code to allow for this?

VBA Code:
Sub SortIndividualCol()
    Dim xRg As Range
    Dim yRg As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Input header range", Type:=8)
    Application.ScreenUpdating = False
    For Each yRg In xRg
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlDescending
            .SetRange ws.Range(yRg, yRg.End(xlDown))
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
Data before sorting:

TestATestBTestCTestDTestE
3​
1​
1​
1​
2​
4​
3​
4​
3​
5​
5​
1​
2​
2​
2​
3​
2​
3​
4​
1​

Data after descending sort, but blank cells are sorted to the top:

TestATestBTestCTestDTestE
5​
5​
4​
4​
4​
3​
3​
3​
3​
3​
2​
2​
2​
2​
2​
1​
1​
1​
1​
1​
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,020
Office Version
365
Platform
Windows
THe simplest way is to amend formula to return 0 instead if empty string (and then supress zero values in the sheet)
IFERROR(H2/I2,0)

There are several ways to suppress zeros
- eg use Conditional Formatting to make the Colour of the Cell font the same as the colour of cell background if cell value is zero
- custom format is another option - see Excel custom number formats | Exceljet
 

sjedi

New Member
Joined
Dec 8, 2019
Messages
11
Office Version
2016
Platform
Windows
Thanks @Yongle, while zeros would help if all the other numbered cells are positive; if all the other numbered cells are a mix of positive and negative (e.g. -1, -2, 3, 4, 5), the zeros will be incorrectly sorted in between the numbered cells.
Is there a more generic method of solving this, that also takes care of the case with negative and positive numbered cells?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,684
Office Version
365
Platform
Windows
If I start off with this
Book1
ABCDE
1TestATestBTestCTestDTestE
231112
3
 
 
434
4
 
 
355
512223
623
 
41
Sheet1
Cell Formulas
RangeFormula
A3:B4A3=IFERROR(H2/I2,"")
C6C6=IFERROR(J6/K6,"")


After running your code I get

Book1
ABCDE
1TestATestBTestCTestDTestE
233455
322344
411233
5
 
 
122
6
 
 
 
11
Sheet1
Cell Formulas
RangeFormula
A5:B5, A6:B6A5=IFERROR(H4/I4,"")
C6C6=IFERROR(J6/K6,"")
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,020
Office Version
365
Platform
Windows
would help if all the other numbered cells are positive; if all the other numbered cells are a mix of positive and negative (e.g. -1, -2, 3, 4, 5), the zeros will be incorrectly sorted in between the numbered cells.
That makes sense

How about changing the formula to return a big negative number and suppressing that
IFERROR(H2/I2,-99999)
 

sjedi

New Member
Joined
Dec 8, 2019
Messages
11
Office Version
2016
Platform
Windows
@Fluff, you are right if the cell contains the formula =IFERROR(H4/I4,"").
However, my actual formula for the blank cells is an index/match formula to reference cells which have the formula = IFERROR(H4/I4,""), so some of the cells refer to a Blank value i.e. ""
Is there any way to amend my sort code to move these blank cells below the numbered cells?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,684
Office Version
365
Platform
Windows
If the cells don't have IFERROR(H2/I2,"") as stated in your op, what do they have?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,098
Messages
5,466,637
Members
406,493
Latest member
Hazem Hassan

This Week's Hot Topics

Top