CODEWITHSUNDEEP
sortingexcelexcel-2007vba
Kurt Wagner
Kurt Wagner

Reputation: 3315

How to Sort Excel Column S.T. Rows w/ Null/Empty Column Cell Go On Top?

I have several rows that have data and 'flags' that are raised adjacent to the data when a macro is run. For example:
First Name | Last Name | Flag 

John  | Smith  | Needs a Bath 
Cindy | LuWho  | 
Bob   | Loblaw | Needs a Bath

Goal:
I want the rows w/o flags (ie where column C == NULL/Empty string) to be sorted to the top and then sort by column B by A->Z to get this:

Cindy | LuWho |
Bob | Loblaw | Needs a Bath
John | Smith | Needs a Bath

What I've Tried:
Using Excel 2007's 'Sort', I've done Sort By (Column C), Sort On (Values) Sort By (A to Z) and (Z to A). Both A to Z and Z to A result in the flagged rows on top, not the bottom.

Before:enter image description here After:
enter image description here

I ultimately want the code, but I'm currently trying to figure out how to do it by hand so I can then get the code through Excel's 'Record Macro'.

Upvotes: 1

Views: 13598

Answers (3)

djhurio
djhurio

Reputation: 5536

One of the solutions is to replace blanks with single quote (') before sorting. It is not visible but it is not NULL. See the code example:

Public Sub Sort_blanks()

    Dim lastrow As Integer

    ' The number of the last row
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row

    ' Replace blanks with with single quote
    Range("C2:C" & lastrow).Select
    Application.DisplayAlerts = False
    On Error Resume Next
    Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "'"
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' Sort
    Range("A:C").Sort key1:=Range("C:C"), key2:=Range("B:B"), _
        order1:=xlAscending, order2:=xlAscending, Header:=xlYes

End Sub

Upvotes: 0

Kurt Wagner
Kurt Wagner

Reputation: 3315

I ended coming up with a solution that IMO, is more elegant than @Poweruser 's creating another column, populating it, hiding it, and then using sort on the hidden column. My method utilizes font color changes based on conditional formatting and sorts off of that.

  1. Select desired range of column that contains blank values you want to sort by
  2. Use Conditional Formatting>New Rule>'Use a formula to determine which cells to format' and in the textbox use the formula =IF(INDIRECT("RC",0)="",TRUE,FALSE)
  3. Select 'Format...', select 'Font' tab and change the Font color to something not black or 'Automatic', apply changes
  4. Using 'Sort', have 'Sort By' be the column with the blank cells, 'Sort On' be 'Font Color', and for 'Order By' change Automatic to whatever color you selected and have it be 'On Top'

With a little bit of tinkering of the recorded macro, I got the following working code (which also sorts by value another column after sorting for the 'blank' cells):

For oRow = 2 To iFinalRow
    ActiveWorkbook.ActiveSheet.Cells(oRow, 5).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(INDIRECT(""RC"",0)="""",TRUE,FALSE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
Next oRow

'Sort
ActiveWorkbook.ActiveSheet.SORT.SortFields.Clear
ActiveWorkbook.ActiveSheet.SORT.SortFields.Add(Range("E:E"), _
    xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(31, 73, 125)
ActiveWorkbook.ActiveSheet.SORT.SortFields.Add _
    Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.ActiveSheet.SORT
    .SetRange Range("A:F")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Upvotes: 0

PowerUser
PowerUser

Reputation: 11791

Per my comment above, the problem is that you are using a formula that evaluates to an empty string. If the field was actually empty, you'd have the behavior you are looking for.

Here's a dirty-but-it-works approach:

  1. Make a new column to the right. Use the formula =IF(C2<>"",2,1) and fill down.
  2. Hide the column from prying eyes (just right-click on the grey column header at top to hide it)
  3. Sort by this column instead of C.

Upvotes: 3

Related Questions