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?
Data before sorting:
Data after descending sort, but blank cells are sorted to the top:
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 = TrueData before sorting:
| TestA | TestB | TestC | TestD | TestE |
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:
| TestA | TestB | TestC | TestD | TestE |
5 | 5 | |||
4 | 4 | 4 | ||
3 | 3 | 3 | 3 | 3 |
2 | 2 | 2 | 2 | 2 |
1 | 1 | 1 | 1 | 1 |
