Array length

The argument to CountA must be a Range object. Sometimes documentation says that it can be an array, but that is an Excel sheet array, not a VBA array variable.

In your case, the array starts at element 0, so the number of elements is the upper bound + 1.

UBound(myRows) + 1

If you have bounds that are not the default, you need this:

Dim AnotherArray(5 To 10)

UBound(AnotherArray) - LBound(AnotherArray) + 1

Not entirely sure why you need to use an array.

Option Explicit

Sub CountLoggedOn()

Dim lLR As Long, lLoggedOn As Long
Dim awf As WorksheetFunction: Set awf = WorksheetFunction

' determine how many cells to include (using column A)
lLR = Range("A" & Rows.Count).End(xlUp).Row
' count the non-blank cells for the next column
lLoggedOn = awf.CountA(Range("A1").Offset(0, 1).Resize(lLR))
' display the count
MsgBox lLoggedOn

End Sub

This could be made into a function and, if required, called from the worksheet.

Formula:

=fCountLoggedOn()

Function fCountLoggedOn()

Dim lLR As Long, lLoggedOn As Long
Dim awf As WorksheetFunction: Set awf = WorksheetFunction

' determine how many cells to include (using column A)
lLR = Range("A" & Rows.Count).End(xlUp).Row
' count the non-blank cells for the next column
lLoggedOn = awf.CountA(Range("A1").Offset(0, 1).Resize(lLR))
' display the count
fCountLoggedOn = lLoggedOn

End Function

Sub sTest_fCountLoggedOn()

MsgBox fCountLoggedOn

End Sub

This relates specifically to column A although it could be amended to work with any range.


Regards, TMS

Option Explicit

Sub CountLoggedOn()

Dim lLR As Long, lLoggedOn As Long
Dim awf As WorksheetFunction: Set awf = WorksheetFunction

' determine how many cells to include (using column A)
lLR = Range("A" & Rows.Count).End(xlUp).Row
' count the non-blank cells for the next column
lLoggedOn = awf.CountA(Range("A1").Offset(0, 1).Resize(lLR))
' display the count
MsgBox lLoggedOn

End Sub

Function fCountLoggedOn()

Dim lLR As Long, lLoggedOn As Long
Dim awf As WorksheetFunction: Set awf = WorksheetFunction

' determine how many cells to include (using column A)
lLR = Range("A" & Rows.Count).End(xlUp).Row
' count the non-blank cells for the next column
lLoggedOn = awf.CountA(Range("A1").Offset(0, 1).Resize(lLR))
' display the count
fCountLoggedOn = lLoggedOn

End Function

Sub sTest_fCountLoggedOn()

MsgBox fCountLoggedOn

End Sub

Sub sLastLoggedOn()

Dim lLR As Long
' determine last cell (using column B)
lLR = Range("B" & Rows.Count).End(xlUp).Row

MsgBox "Row " & lLR & " :  Value: " & Range("B" & lLR).Value

End Sub

Function fLastLoggedOn()

Dim lLR As Long
' determine last cell (using column B)
lLR = Range("B" & Rows.Count).End(xlUp).Row

fLastLoggedOn = "Row " & lLR & " :  Value: " & Range("B" & lLR).Value

End Function

Sub sTest_fLastLoggedOn()

MsgBox fLastLoggedOn

End Sub

Regards, TMS

OK, I'm struggling too. I have no idea what your data looks like, what the end to end code looks like, or what you are trying to achieve.

When you are given code, you know all this ... but we don't. So it's hard to work out what it needs to do.


Regards, TMS