In Microsoft Excel, sorting a column or multiple columns in ascending order (A-Z) sends empty cells to the bottom of each list in the column. Moreover, it provides no options for dealing with empty cells in its sorting functions.
How can one send them to the top when sorting a selection of columns? Would like to achieve a result similar to this;
Edit, if you have Excel 365 you can use SORTBY function.
You could do it manually using search and replace, replacing blanks with say 1s, which sort ahead of text and replacing 1s with blanks. You could do it in VBA as well.
Sub SortTableWithBlanksOnTop()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A1:C" & lastRow)
rng.Replace What:="", Replacement:="1", _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("A1:A" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("B1:B" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("C1:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rng
.Header = xlYes ' Assuming you have headers
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
rng.Replace What:="1", Replacement:="", _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub
ws.[A:A].Thank you so much everyone for taking the time to add your comments and answers. They helped point me in the right direction. Indeed @Michal's answer works for a fixed number of columns and many may find it useful. What was needed was specifically a VBA code for a selection of columns, whenever a user arbitrarily selects any range of cells spanning any number of columns and have them sorted correctly.
After some research, this crafted solution works and works for anyone.
Sub SortBlanksOnTop()
On Error Resume Next
' Set up your variables and turn off screen updating.
Dim w As Worksheet
Dim r As Range ' Working range
Dim v As Double ' Value for blank cells
Application.ScreenUpdating = False
' Set title for the range selection user dialog box.
t = "Selection Range"
' Get the sheet.
Set w = ActiveSheet
' Request and store range from user.
Set r = Application.InputBox("Range", t, Application.Selection.Address, Type:=8)
' Create a new least value by subtracting from the least value in the selection.
' Doubles are being used because Ms Excel sorting ranks them before alphabets in ascending order.
v = Application.WorksheetFunction.Small(r, 1) - 1
'Substitute all blank cells in the selection range with this new value.
r.SpecialCells(xlCellTypeBlanks) = v
' Clear any existing sort fields.
w.Sort.SortFields.Clear
' Add sort field for each column in the selection range.
For Each Column In r.Columns
w.Sort.SortFields.Add Key:=Column, Order:=xlAscending
Next Column
' Apply the sort
With w.Sort
.SetRange r
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Revert to blank cells.
r.Replace What:=v, Replacement:="", LookAt:=xlWhole
' Turn screen updating back on.
Application.ScreenUpdating = True
End Sub
Feel free to add conditions in the For Each loop to exclude any kinds of cells, columns or rows.
Hope to save someone's time.
Non-formula solution (not my original idea):
Or with SORTBY, assuming all input is text (or blanks), by adding an empty string first -
Enter in a cell with enough empty cells to hold the sorted table:
=LET(tbl, A2:C12 & "",
SORTBY(tbl, INDEX(tbl, , 1), , INDEX(tbl, , 2), , INDEX(tbl, , 3), ))
