重复浏览特定列中的单元格列表，以查找值列表何时结束

Question

我正在尝试计算包含值的列中的单元格数。脚本的想法是遍历值列表(字符串或长(并发现单元格是否为空。当它到达一个空单元格时，它将停止计数，然后在该列中包含值的单元格总数 msgbox。

我之前已经使用以下代码给出了这个，并且我一直没有得到 Excel 的响应。我不确定我哪里出错了。

    Sub Tester()

    Blockquote
Dim CellNumber As Long
Dim CellLetter As String
Dim Cell As String
Dim Total as Long
Dim cellEmpty As Boolean
Letter = "A"
Number = 1
Cell = CellLetter & CellNumber
Do
If Not IsEmpty(Range(Cell).Value) Then
CellNumber = CellNumber + 1
Total = Total + 1
ElseIf IsEmpty(Range(Cell).Value) Then
cellEmpty = True
End If
Loop While cellEmpty = False

End Sub

Answer 1

你可以试试：

Option Explicit
Sub test()
    Dim Lastrow As Long, i As Long, Counter As Long
    'Change Sheet name (Sheet1) if neede
    With ThisWorkbook.Worksheets("Sheet1")
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Counter = 0
        'Change starting point (1) if needed
        For i = 1 To Lastrow
            'Change Column (A) if needed
            If .Range("A" & i).Value = "" Then
                MsgBox "The total number of non empty cells are " & Counter & "."
            Else
                Counter = Counter + 1
                    If i = Lastrow Then
                        MsgBox "The total number of non empty cells are " & Counter & "."
                    End If
            End If
        Next i
    End With
End Sub

Answer 2

感谢您的回复。我用以下内容对其进行了排序：

Sub Tester()
Dim CellNumber As Integer
Dim CellLetter As String
Dim Cell As String
Dim Total As Long
Dim cellEmpty As Boolean
CellLetter = "A"
CellNumber = 1
Total = 0
Do
Cell = CellLetter & CellNumber
If Not IsEmpty(Range(Cell).Value) Then
CellNumber = CellNumber + 1
Total = Total + 1
ElseIf IsEmpty(Range(Cell).Value) Then
cellEmpty = True
End If
Loop While cellEmpty = False
MsgBox Total
End Sub