如何通过 VBA 仅循环遍历 Excel 工作表中一列中的非空单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45670505/
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
How to loop through only the non-empty cell in a column in an excel sheet via VBA?
提问by Aqqqq
According to this website.
根据这个网站。
I think this should work:
我认为这应该有效：
Dim cell As Range
For Each cell In xxxSheet.Range("B:B").SpecialCells(xlCellTypeFormulas, xlNumbers)
'Do sth.
Next
which does not work. Is there something missing?
这不起作用。有什么遗漏吗？
回答by Micha? Turczyn
This should be working solution:
这应该是有效的解决方案：
For Each cell In xxxSheet.Range("B:B")
If Not IsEmpty(cell) Then
'do sth
End If
Next
Also, if you want to loop until last filled cell, you could use following:
此外，如果您想循环到最后一个填充的单元格，您可以使用以下内容：
xxxSheet.Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
xxxSheet.Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
instead of
代替
xxxSheet.Range("B:B").
xxxSheet.Range("B:B").
回答by Vityata
It does not work, because you do not have formulas on column B. Put some formulas and some constants and try this:
它不起作用，因为您在 column 上没有公式B。输入一些公式和一些常量，然后试试这个：
Option Explicit
Public Sub TestMe()
Dim myCell As Range
Dim myRange As Range
Set myRange = Worksheets(1).Columns("B:B").SpecialCells(xlCellTypeFormulas, xlNumbers)
For Each myCell In myRange
Debug.Print myCell.Address
Next
Set myRange = Worksheets(1).Columns("B:B").SpecialCells(xlCellTypeConstants, xlNumbers)
For Each myCell In myRange
Debug.Print myCell.Address
Next
End Sub
The first loop would print the addresses of the formula cells, the second the addresses of the constants.
This is the ozgrid explanation about SpecialCells:
第一个循环将打印公式单元格的地址，第二个循环将打印常量的地址。这是关于 ozgrid 的解释SpecialCells：
回答by JCKE
The problem is SpecialCells(xlCellTypeFormulas, xlNumbers)is returning only cells with formulas that make numbers (ie. =1+2).
问题是SpecialCells(xlCellTypeFormulas, xlNumbers)仅返回带有生成数字的公式的单元格（即。=1+2）。
To keep things efficient, you only need to check up to the last filled row
为了保持高效，您只需要检查到最后填充的行
For Each cell In xxxSheet.Range("B1", Cells(Rows.Count, 2).End(xlUp))
If Not IsEmpty(cell) Then
'Do sth.
End If
Next
If you really want you can use SpecialCells()to have a range containing no blanks to loop through. If you only have formulas or only constants, you could use SpecialCells(xlFormulas)or SpecialCells(xlConstants)respectively, but for a more general use case you will have to do do a combination of the two.
如果你真的想要你可以使用SpecialCells()一个不包含空格的范围来循环。如果您只有公式或只有常量，则可以分别使用SpecialCells(xlFormulas)或SpecialCells(xlConstants)，但对于更一般的用例，您必须将两者结合使用。
Dim cell As Range
Dim searchRange As Range
' SpecialCells errors when there aren't cells instead of giving a useful value
On Error Resume Next
Set searchRange = xxxSheet.Range("B:B").SpecialCells(xlFormulas)
Set searchRange = xxxSheet.Range("B:B").SpecialCells(xlConstants)
Set searchRange = Union(xxxSheet.Range("B:B").SpecialCells(xlConstants), _
xxxSheet.Range("B:B").SpecialCells(xlFormulas))
On Error GoTo 0
If searchRange Is Not Nothing Then ' Only continue if no blanks
For Each cell In searchRange
'Do sth.
Next
End If
