Count number of rows in a different Excel Sheet

Question

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?

Where do you define `Sheet3`? – Dan Wagner May 07 '14 at 16:50 — Dan Wagner, May 07 '14 at 16:50

Dan Wagner · Accepted Answer · 2014-05-07T17:37:20.910

3

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

edited May 07 '14 at 17:37

answered May 07 '14 at 16:57

Dan Wagner

2,693
2
13
18

1

You need a '.' before the first Range also. – Lance Roberts May 07 '14 at 17:13
Great eye @LanceRoberts, I have updated the response above to qualify the first `range` object too. Thanks! – Dan Wagner May 07 '14 at 17:37

Count number of rows in a different Excel Sheet

1 Answers1

Linked