计算带有空白单元格的行数（Excel / VBA）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15885764/
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
Count number of rows with blank cells (Excel / VBA)
提问by Samuel Barrow
Hi i'm trying to count the number of rows which contain blank cells. (I know there are 963 blank cells, i just don't know how many rows they're spread across)
嗨，我正在尝试计算包含空白单元格的行数。（我知道有 963 个空白单元格，我只是不知道它们分布了多少行）
I've very limited knowledge of VBA and am finding it difficult to implement.
我对 VBA 的了解非常有限，并且发现它很难实现。
The way i'm thinking...
我想的方式...
Two for loops.
两个 for 循环。
Outer loop will cycle down the rows
外循环将循环向下行
Inner loop will cycle across each cell in the row
内循环将在行中的每个单元格中循环
When a blank cell is encountered in a row a counter will increment by one and we'll move to the next row.
当一行中遇到一个空白单元格时，计数器将增加一，我们将移至下一行。
回答by Stepan1010
Here's a fairly easy way to do it without VBA:
这是一种相当简单的方法，无需 VBA：
回答by tbur
You actually don't need any loops to do this.
你实际上不需要任何循环来做到这一点。
This sample checks row A. Change "Const column_to_test" number to the column number you wish to check for blank cells.
此示例检查 A 行。将“Const column_to_test”编号更改为要检查空白单元格的列编号。
Sub countblank()
'This will count the number of rows that have a blank cell in column "A"
Const column_to_test = 1 'first column (A)
Dim r As Range
Set r = Range(Cells(1, column_to_test), Cells(Rows.Count, column_to_test).End(xlUp))
MsgBox ("There are " & r.SpecialCells(xlCellTypeBlanks).Count & " Rows with blank cells")
'You may want to select those rows (for deletion?)
r.SpecialCells(xlCellTypeBlanks).EntireRow.Select 'change .Select to .Delete
End Sub
回答by Santosh
Try below code
试试下面的代码
Sub countBlankInRow()
Dim counter As Long
For i = 1 To 1000 ' specify your rows
For j = 1 To 26 ' specify your columns
If Cells(i, j) <> "" Then
Exit For
Else
If j = 26 Then counter = counter + 1 ' Alter the col no accordingly
End If
Next
Next
MsgBox counter
End Sub
