vba 如何对带有空/空列单元格的 Excel 列 ST 行进行排序？

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

提问by Kurt Wagner

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:

目标：
我希望将没有标志的行（即列 C == NULL/空字符串）排序到顶部，然后按 A->Z 按列 B 排序以得到这个：

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.

我尝试过的：
使用 Excel 2007 的“排序”，我完成了排序方式（C 列）、排序方式（值）、排序方式（A 到 Z）和（Z 到 A）。A 到 Z 和 Z 到 A 都会导致标记行位于顶部，而不是底部。

Before:After:

之前：之后：

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'.

我最终想要代码，但我目前正试图弄清楚如何手动完成，以便我可以通过 Excel 的“记录宏”获取代码。

采纳答案by Kurt Wagner

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.

我最终提出了一个解决方案，IMO 比 @Poweruser 创建另一列、填充它、隐藏它，然后在隐藏列上使用 sort 更优雅。我的方法利用基于条件格式的字体颜色更改并对其进行排序。

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'

1. 选择包含要作为排序依据的空白值的所需列范围
2. 使用条件格式>新规则>“使用公式确定要设置格式的单元格”并在文本框中使用该公式 =IF(INDIRECT("RC",0)="",TRUE,FALSE)
3. 选择“格式...”，选择“字体”选项卡并将字体颜色更改为非黑色或“自动”，应用更改
4. 使用“排序”，将“排序依据”设为包含空白单元格的列，“排序依据”设为“字体颜色”，将“排序依据”设为“自动”为您选择的任何颜色并将其设为“置顶”

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

回答by PowerUser

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-worksapproach:

这是一个肮脏但有效的方法：

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.

1. 在右侧创建一个新列。使用公式=IF(C2<>"",2,1)并填写。
2. 从窥探中隐藏该列（只需右键单击顶部的灰色列标题即可将其隐藏）
3. 按此列而不是 C 排序。

回答by djhurio

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:

解决方案之一是'在排序之前用单引号 ( )替换空格。它不可见，但不是NULL。查看代码示例：

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