Hi

Use it like this

In your example "cells" in the code will use the cells on the activesheet
See the dot before it in my example

Sub check()
Dim myrange As Range
With Sheets("sheet2")
Set myrange = .Range(.Cells(1, 1), .Cells(4000, 1))
End With
MsgBox Application.CountA(myrange)
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"chandran19" wrote in message ...

I am using the following code to count the no of entries in colA in
sheet2
I am currently in sheet1

Sub check()
Set myrange = Sheets("sheet2").Range(Cells(1, 1), Cells(4000, 1))
MsgBox Application.CountA(myrange)
End Sub

This gives an error as follows

"run time error 1004

application or object defined error"

but when I am in sheet2 and run this macro there is no error

how to avoid this?


--
chandran19
------------------------------------------------------------------------
chandran19's Profile: http://www.excelforum.com/member.php...o&userid=14497
View this thread: http://www.excelforum.com/showthread...hreadid=261217