vba 自动过滤以查找空白单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16014672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
AutoFilter to find blank cells
提问by user1643333
I am trying to apply an autofilter in VBA for three different criterias in the same field. Once I have applied the filter I would like to find all those cells that are blank, can anyone advise?
我正在尝试在 VBA 中为同一领域的三个不同标准应用自动过滤器。应用过滤器后，我想找到所有空白的单元格，有人可以建议吗？
Sub ApplyAutoFiler()
Dim ws As Worksheet
Dim I, j, NumberOfErrors As Long
IsErrors = False
Set ws = Sheets("Assessments")
NumberOfErrors = 0
Dim Z As Range
Set Z = Cells(4, 3).EntireColumn.Find("*", SearchDirection:=xlPrevious)
If Not Z Is Nothing Then
NumberOfRows = Z.Row
End If
For I = 4 To NumberOfRows
With ws
.AutoFilterMode = False
.Range("W4:AA4").AutoFilter Field:=1, Criteria1:=Array("A", "B", "C"), Operator:=xlFilterValues
.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = 65535
.AutoFilterMode = False
End With
Next I
End Sub
I ended up doing this as a nested if statement
我最终把它作为一个嵌套的 if 语句
If Range("W" & i).Value = "A" Or Range("W" & i).Value = "B" Or Range("W" & i).Value = "C" Then
If Range("AD" & i).Value = "" Then
Range("AD" & CStr(i)).Interior.ColorIndex = 3
NumberOfErrors = NumberOfErrors + 1
End If
End If
回答by sous2817
This seemed to get me close (it also assumes you have a worksheet called "Assessments"):
这似乎让我很接近（它还假设您有一个名为“评估”的工作表）：
Sub ApplyAutoFiler()
Dim ws As Worksheet
Set ws = Sheets("Assessments")
With ws
.AutoFilterMode = False
.Range("A:AZ").AutoFilter Field:=23, Criteria1:=Array("a", "b", "c"), Operator:=xlFilterValues
.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = 65535
.AutoFilterMode = False
End With
End Sub
回答by tiedied61
Something I just discovered today about filtering for blanks using VBA code. Be sure to include this in ALL code where you need to have blank cells:
我今天刚刚发现的关于使用 VBA 代码过滤空白的东西。确保在所有需要空白单元格的代码中包含它：
' Get Rows with blanks
WorkRange.AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="=" & ""
' Hides Rows with blanks ... same idea with the "<>" for operator
WorkRange.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlOr, Criteria2:="<>" & ""
The first criteria gets true blank cells and those cells with hidden/non-printable characters, the 2nd criteria gets those cells containing an empty string. Excel user-interface handles this nicely, but VBA code requires both criteria.
第一个条件获取真正的空白单元格和具有隐藏/不可打印字符的单元格，第二个条件获取包含空字符串的单元格。Excel 用户界面很好地处理了这个问题，但 VBA 代码需要这两个条件。
This undocumented caveat just cost me several hours of debugging, not to mention a few choice words from my manager about "I thought we were removing the blanks from these columns..."
这个未记录的警告只花费了我几个小时的调试时间，更不用说我的经理的一些选择词“我以为我们正在从这些列中删除空白......”
Just thought I would share, in the hopes of saving you all some headaches.
只是想我会分享，希望能为大家省去一些麻烦。
回答by user7377729
I know this treads had been quite long. But just want to share. To filter out blank cells, you could use autofilter using the following criteria:
我知道这个踏板已经很长了。但只是想分享。要过滤掉空白单元格，您可以使用以下标准使用自动过滤器：
Worksheets("sheet name").Range("A1").autoFilter Field:=18, Criteria1:=(Blanks)
"Field" refers to the column numbers. As for "Criteria1", it can be either
“字段”是指列号。至于“Criteria1”，它可以是
Criteria1:=(Blanks)
or
或者
Criteria1:="="
or
或者
Criteria1:=""
回答by Siddharth Rout
You don't need VBA for this. You can use Conditional Formatting for this. See this example
为此，您不需要 VBA。您可以为此使用条件格式。看这个例子
In the CF rule, set this formula
在CF规则中，设置这个公式
=AND($AA5="",OR($W5="a",$W5="b",$W5="c"))
=AND($AA5="",OR($W5="a",$W5="b",$W5="c"))
ScreenShot
截屏
If you still want VBA then see this
如果你仍然想要 VBA，那么看看这个
Sub Sample()
Dim blnkRange As Range, rng As Range, aCell As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Assessments")
With ws
'~~> Sample range for testing purpose
Set rng = .Range("W4:AA11")
.AutoFilterMode = False
With rng
'~~> Filter on "a","b","c"
.AutoFilter Field:=1, Criteria1:=Array("a", "b", "c"), Operator:=xlFilterValues
'~~> Then filter on blanks on Col AA
.AutoFilter Field:=5, Criteria1:="="
'~~> Using offset. Assuming that Row 4 has headers
Set blnkRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells
End With
.AutoFilterMode = False
End With
'~~> This will give you the blank cells in Col AA
If Not blnkRange Is Nothing Then
For Each aCell In blnkRange
'~~> Color the blank cells red in Col AA
If aCell.Column = 27 Then aCell.Interior.ColorIndex = 3
Next
End If
End Sub
