Excel VBA use input box to change a row number

Hi

I have Excel  VBA code that finds the last row in the sheet. If this is not the row that
the user wants to loop to, I want the user to be able to enter  row number
What VBA code would I use to show an input box that facilitates this?

Thanks
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Martin LissConnect With a Mentor Older than dirtCommented:
This corrects line 5 in the above, adds the last used line as the default and checks for invalid input.

Dim ULoop As Integer
Dim i As Long

On Error Resume Next
ULoop = InputBox("Until which row would you like to loop?", "Loop To", ActiveSheet.UsedRange.Rows.Count)
If ULoop = 0 Then
    MsgBox "Invalid or no selection"
    Exit Sub
End If
On error Goto 0
For i = 1 To ULoop
     ' do your thing
Next

Open in new window

0
 
MacroShadowConnect With a Mentor Commented:
Something like this:
Dim ULoop as Integer

ULoop = Inputbox("Until which row would you like to loop?")

For i 1 to ULoop
     ' do your thing
Next

Open in new window

0
 
Patrick MatthewsConnect With a Mentor Commented:
Never, ever, ever us an Integer data type to hold a row number.  The moment you get to Row 32768, you are looking at an overflow error.

Always use Long for this.  Always.  Hell, I'll go further: it never makes sense to use Integer for anything.  Just use Long, especially since for many operations VBA will implicitly convert your Integer into a Long anyway, and then have to convert it back.
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
MacroShadowCommented:
I stand corrected.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks for  collective answer
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
All Courses

From novice to tech pro — start learning today.