CODEWITHSUNDEEP
vbaexcel
A.V. Pham
A.V. Pham

Reputation: 23

VBA - Run-time error '1004' - Method 'Range' of object'_Global' failed

Excuse me if you guys see this question again. However, I have searched for similar topics in this site but can't figure out my problem.

I have a VBA code snipet with a line throwing back an error:

Sub test()
Dim rng As Range

Set rng = Application.InputBox("Select range: ", "Select range", Type:=8)

MsgBox (Range("rng").Rows.Count)

End Sub

My intention is to prompt user to select a range and to count number of rows in that range.

If I pre-define the name range "rng" instead of selecting range in run-time like the code below, it will return the number of rows without errors.

Sub test()
Dim rng As Range

MsgBox (Range("rng").Rows.Count)

End Sub

Could someone please tell me the problem with the range selected by user so that it can't return number of rows? Doesn't the "rng" range defined by user have a "Rows" property?

Thank you for your help.

Upvotes: 1

Views: 536

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

rng is already a valid range and it has it's own Rows propery which you can access like this...

Also, consider to handle the case if user doesn't select any range and hit Cancel on InputBox.

Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox("Select range: ", "Select range", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then MsgBox rng.Rows.Count

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

Change:

MsgBox (Range("rng").Rows.Count)

To:

MsgBox rng.Rows.Count

Upvotes: 2

Related Questions