Counting Range Data From Different Sheet

[chandran19]

I am using the following code to count the no of entries in colA i
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

 

[Wolf]

Hi chandran19,

something like:
Sub check()
Dim myRange As Range
Sheets("sheet2").Activate
Set myRange = Range(Cells(1, 1), Cells(4000, 1))
MsgBox Application.CountA(myRange)
End Sub

Best regards

Wolf

 

[Ron de Bruin]

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