Excel VBA WorksheetFunction.CountA - does not work after upgrading to Office 2010

Question

The following code snippet works in Excel until 2010:

myRange = Range("A:A") NumRows = Application.CountA(myRange) 

Column A contains 38 cells containing text / values. When the code runs in Excel 2007, NumRows correctly evaluates to 38, however it (erroneously) evaluates 65,536 in Excel 2010.

Entering the CountA function inside the cell works fine in both versions.

A similar topic was question 16696891 , but there was no answer, and there were proposals, I think, red herring ...

Any ideas?

Answer 1

I don’t know exactly what the problem is, because I cannot get your code to work as it is written. Two things seem obvious:

• You seem to rely on VBA to determine the types of variables and change them accordingly. This can be confusing if you are not careful, because VBA can assign a type to a variable that you did not plan. In your code, myRange should be assigned to myRange . Since the Range type is an object in VBA, it must be Set , for example: Set myRange = Range("A:A")
• Your use of the worksheet function CountA() should be called using .WorksheetFunction

If you are not already doing this, consider using the Option Explicit option at the top of your module and typing variables using Dim statements, as I did below.

The following code works for me in 2010. Hope 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.

Answer 2

This answer from another forum solved the problem.

(replace your own range for "I: I" shown here)

Re: CountA does not work 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. 

Answer 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 

Answer 4

There seems to be 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 returns 32

It looks like VB7 no longer counts COUNTA COUNTA.

Answer 5

This may be obvious, but by specifying a range and not including a workbook or worksheet, it may try CountA () on the whole worksheet. I believe that for a complete solution to these problems, many headaches persist.