WorksheetFunction.CountA - not working post upgrade to Office 2010
Solution 1
I'm not sure exactly what your problem is, because I cannot get your code to work as written. Two things seem evident:
- It appears you are relying on VBA to determine variable types and modify accordingly. This can get confusing if you are not careful, because VBA may assign a variable type you did not intend. In your code, a type of
Range
should be assigned tomyRange
. Since aRange
type is an object in VBA it needs to beSet
, like this:Set myRange = Range("A:A")
- Your use of the worksheet function
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.
Solution 2
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.
Solution 3
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
Richard Ell
Updated on July 16, 2022Comments
-
Richard Ell almost 2 years
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?
-
Richard Ell over 9 yearsHi Thanks both. Sorry, should have made it clear - the range is set using the Set keyword. Actually you don't need
.WorksheetFunction
, in factApplication.CountA
should work on its own. On the PC in question,Application.WorksheetFunction.CountA(Range("A:A"))
shows 65,536 even though only 38 cells contain anything. I cannot replicate on my own desk. I thought it might be some sort of issue with upgrade from one version of office to another. The "new" PC has Office 2010 on Windows 7, and was upgraded from Office 2007 on Windows 2003. -
Instant Breakfast over 9 yearsCould it be an issue with the specific spreadsheet. Try selecting all of the "empty" cells, deleting them, and saving. If that does not result in the expected "38" result, I'm stumped.