COUNTA function causing syntax error in macro...

B

Birmangirl

A colleague is using XL2003 and has used the following formulae as part of a
macro - he's trying to test for non blank cells in a dynamic range:

ExtractLength=(COUNTA('Blank'!$A:$A)-1)

It's causing a Compile Error:Syntax Error

Is this because the formulae as written contains a circular reference? How
best to advise - any help gratefully accepted; macros really aren't my strong
point!

Amanda
 
J

JMB

Try this version:

ExtractLength= Application.COUNTA(Sheets("Blank").Range("A:A"))-1
 
R

Ron Coderre

Try something like this:

ExtractLength=(WorksheetFunction.CountA(Range("'Blank'!$A:$A"))-1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
B

bj

I am not sure what your colleage is trying to do
but try
exact length = application.worksheetfunction.counta(('Blank'!$A:$A)-1
counta() is not a VB function
 
B

Birmangirl

Cheers Ron (and everyone else who responded so quickly). Your answer solved
the problem.

Amanda
 
Top