vba 检测范围是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10811121/
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
Detect if range is empty
提问by Kano
I want to check if a range in Excel is empty.
我想检查 Excel 中的范围是否为空。
How do I write in VBA code:
我如何用 VBA 代码编写：
If Range("A38":"P38") is empty
回答by Kano
Found a solution from the comments I got.
从我得到的评论中找到了解决方案。
Sub TestIsEmpty()
If WorksheetFunction.CountA(Range("A38:P38")) = 0 Then
MsgBox "Empty"
Else
MsgBox "Not Empty"
End If
End Sub
回答by Sharunas Bielskis
IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants. (https://msdn.microsoft.com/en-us/library/office/gg264227.aspx) . So you must check every cell in range separately:
如果变量未初始化或显式设置为 Empty，则 IsEmpty 返回 True；否则，它返回 False。如果表达式包含多个变量，则始终返回 False。IsEmpty 仅返回对变体有意义的信息。（https://msdn.microsoft.com/en-us/library/office/gg264227.aspx）。因此，您必须分别检查范围内的每个单元格：
Dim thisColumn as Byte, thisRow as Byte
For thisColumn = 1 To 5
For ThisRow = 1 To 6
If IsEmpty(Cells(thisRow, thisColumn)) = False Then
GoTo RangeIsNotEmpty
End If
Next thisRow
Next thisColumn
...........
RangeIsNotEmpty:
Of course here are more code than in solution with CountA function which count not empty cells, but GoTo can interupt loops if at least one not empty cell is found and do your code faster especially if range is large and you need to detect this case. Also this code for me is easier to understand what it is doing, than with Excel CountA function which is not VBA function.
当然，这里的代码比使用 CountA 函数计算非空单元格的解决方案中的代码更多，但是如果找到至少一个非空单元格，GoTo 可以中断循环，并且更快地执行您的代码，特别是如果范围很大并且您需要检测这种情况。此外，与不是 VBA 函数的 Excel CountA 函数相比，这段代码对我来说更容易理解它在做什么。
回答by DeerSpotter
Dim M As Range
Set M = Selection
If application.CountIf(M, "<>0") < 2 Then
MsgBox "Nothing selected, please select first BOM or Next BOM"
Else
'Your code here
End If
From experience I just learned you could do:
根据我刚刚了解到的经验，您可以这样做：
If Selection.Rows.Count < 2
Then End If`
Clarification to be provided a bit later (right now I'm working)
稍后提供说明（现在我正在工作）
回答by Marcucciboy2
If you find yourself in a situation where it's absolutely necessary that you loop through each cell in a range instead of using CountA, then it's much faster to first convert that range into an array and loop on that array's values than to loop on many ranges/cells.
如果您发现自己绝对有必要遍历范围中的每个单元格而不是使用CountA，那么首先将该范围转换为数组并循环该数组的值比循环许多范围/单元格要快得多.
Function IsRangeEmpty(ByVal rng As Range) As Boolean
'Converts a range to an array and returns true if a value is found in said array
Dim area As Range
For Each area In rng.Areas
If area.Cells.Count > 1 Then
'save range as array
Dim arr As Variant
arr = area.value
'loop through array
Dim cel As Variant
For Each cel In arr
'if cell is not empty then
If Len(Trim(cel)) > 0 Then
IsRangeEmpty = False
Exit Function
End If
Next cel
Else 'cannot loop on array with one value
'if cell is not empty then
If Len(Trim(area.Value2)) > 0 Then
IsRangeEmpty = False
Exit Function
End If
End If
Next area
IsRangeEmpty = True
End Function
Example of how to use it:
如何使用它的示例：
Sub Test()
Debug.Print IsRangeEmpty(Range("A38:P38"))
End Sub
If Range("A38:P38")is empty, it would print True; otherwise it'd print False.
如果Range("A38:P38")为空，则打印True；否则它会打印False。
回答by TomM
Dim cel As Range, hasNoData As Boolean
hasNoData = True
For Each cel In Selection
hasNoData = hasNoData And IsEmpty(cel)
Next
This will return Trueif no cells in Selectioncontains any data. For a specific range, just substitute RANGE(...)for Selection.
True如果没有单元格Selection包含任何数据，这将返回。对于一个特定的范围内，刚刚替补 RANGE(...)了Selection。
回答by DJK
Another possible solution. Count empty cells and subtract that value from the total number of cells
另一种可能的解决方案。计算空单元格并从单元格总数中减去该值
Sub Emptys()
Dim r As range
Dim totalCells As Integer
'My range To check'
Set r = ActiveSheet.range("A1:B5")
'Check for filled cells'
totalCells = r.Count- WorksheetFunction.CountBlank(r)
If totalCells = 0 Then
MsgBox "Range is empty"
Else
MsgBox "Range is not empty"
End If
End Sub
回答by LiNKeR
This just a slight addition to @TomM'sanswer/ A simple function to check
if your Selection's cells are empty
这只是对@TomM's答案的一点补充/一个简单的功能来检查您的选择单元格是否为空
Public Function CheckIfSelectionIsEmpty() As Boolean
Dim emptySelection As Boolean:emptySelection=True
Dim cell As Range
For Each cell In Selection
emptySelection = emptySelection And isEmpty(cell)
If emptySelection = False Then
Exit For
End If
Next
CheckIfSelectionIsEmpty = emptySelection
End Function
回答by intrixius
This single line works better imho:
恕我直言，这一行效果更好：
Application.Evaluate("SUMPRODUCT(--(E10:E14<>""""))=0")
in this case, it evaluates if range E10:E14 is empty.
在这种情况下，它会评估范围 E10:E14 是否为空。
