WorksheetFunction CountA - not working post upgrade to Office 2010

Question

The following piece of code works in Excel prior to 2010  myRange   Range  quot A A quot   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 to myRange  Since a Range type is an object in VBA it needs to be Set  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

User · Answer

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

[excel] WorksheetFunction.CountA - not working post upgrade to Office 2010

Examples related to excel

Examples related to vba

Examples related to worksheet-function