The following piece of code works in Excel prior to 2010:
myRange = Range("A:A")
NumRows = Application.CountA(myRange)
There are 38 cells containing text/values in column A. When the code is run in Excel 2007, NumRows correctly evaluates to 38, however, it (wrongly) evaluates to 65,536 in Excel 2010.
Entering the CountA
function in-cell works OK in both versions.
Similar thread is question 16696891, but there was no answer and the suggestions were, I think, red herrings...
Any ideas?
This question is related to
excel
vba
worksheet-function
This answer from another forum solved the problem.
(substitute your own range for the "I:I" shown here)
Re: CountA not working in VBA
Should be:
Nonblank = Application.WorksheetFunction.CountA(Range("I:I"))
You have to refer to ranges in the vba format, not the in-excel format.
This code works for me:
Sub test()
Dim myRange As Range
Dim NumRows As Integer
Set myRange = Range("A:A")
NumRows = Application.WorksheetFunction.CountA(myRange)
MsgBox NumRows
End Sub
It may be obvious but, by stating the Range and not including which workbook or worksheet then it may be trying to CountA() on a different sheet entirely. I find to fully address these things saves a lot of headaches.
I'm not sure exactly what your problem is, because I cannot get your code to work as written. Two things seem evident:
Range
should be assigned to myRange
. Since a Range
type is an object in VBA it needs to be Set
, like this: Set myRange = Range("A:A")
CountA()
should be called with .WorksheetFunction
If you are not doing it already, consider using the Option Explicit option at the top of your module, and typing your variables with Dim
statements, as I have done below.
The following code works for me in 2010. Hopefully it works for you too:
Dim myRange As Range
Dim NumRows As Integer
Set myRange = Range("A:A")
NumRows = Application.WorksheetFunction.CountA(myRange)
Good Luck.
It seems there is a change in how Application.COUNTA works in VB7 vs VB6. I tried the following in both versions of VB.
ReDim allData(0 To 1, 0 To 15)
Debug.Print Application.WorksheetFunction.CountA(allData)
In VB6 this returns 0.
Inn VB7 it returns 32
Looks like VB7 doesn't consider COUNTA to be COUNTA anymore.
Source: Stackoverflow.com