Mega Code Archive
Count the blank elements in a range
<![CDATA[
Public Sub Array3()
Dim Data As Variant, X As Variant
Dim Message As String, i As Integer
Data = Range("A1:A20").Value
i = 1
Do
Debug.Print "Lower Bound = " & LBound(Data, i)
Debug.Print "Upper Bound = " & UBound(Data, i)
i = i + 1
On Error Resume Next
X = UBound(Data, i)
If Err.Number <> 0 Then Exit Do
On Error GoTo 0
Loop
Debug.Print "Number of Non Blank Elements = " & WorksheetFunction.CountA(Data)
End Sub
]]>