WorksheetFunction.CountA - not working post upgrade to Office 2010

I'm not sure exactly what your problem is, because I cannot get your code to work as written. Two things seem evident:

1. 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 to myRange. Since a Range type is an object in VBA it needs to be Set, like this: Set myRange = Range("A:A")
2. 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 RangeDim NumRows As IntegerSet myRange = Range("A:A")NumRows = Application.WorksheetFunction.CountA(myRange)

Good Luck.

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 NumRowsEnd Sub