Count number of rows in a different Excel Sheet [d

Question

This question already has an answer here:

• Excel VBA, getting range from an inactive sheet 3 answers

It is in the same workbook. I have a few numbers in a range in Sheet3 and the button is in Sheet2. I want it to count the number of rows and then return the value to me in a msgbox. If I run this with the Sheet3 open it runs fine. If I run it with a different sheet active I get an object error for the counting part. It does insert the 3 in cell A2 no matter what sheet is active.

Sub problem3run()
Dim rngtest As Range

Set rngtest = Sheet3.Range("A2")

rngtest.Value = 3

Dim rngacount As Integer
rngacount = Sheet3.Range(Range("A4"), Range("A4").End(xlDown)).Rows.Count

MsgBox (rngacount)

End Sub

What is messed up in this?

Answer 1

Two issues here. The first is that data type Integer can't hold all the rows in certain excel files, so you'll want to use Long instead. The second is that you are defining rngacount with unqualified range objects. Try this:

Sub problem3run()
Dim rngtest As Range

Set rngtest = Sheet3.Range("A2")

rngtest.Value = 3

Dim rngacount As Long '<~ use Long here rather than Integer
With Sheet3 '<~ use a With here to qualify ranges
    rngacount = .Range(.Range("A4"), .Range("A4").End(xlDown)).Rows.Count
End With

MsgBox (rngacount)

End Sub