Range

[Nimish]

I want to take a range that I have inputted from a input box and get
row ID numbers. the range is written as follows:

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select a range!", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Else
MsgBox myRng.Address
End If

I want to take this range and identify the first and last row number
(actual row ID in excel).

I am having trouble with invalid code when I make variables to identify
this. This is going into a For statement to run code from.

 

[Dave Peterson]

Is is single area range?

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select a range!", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Else
MsgBox myRng.Address & vblf _
& myrng.row & vblf _
& myrng.cells(myrng.cells.count).row
End If

=======
If you're going to loop through those rows:

dim myRow as range

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select a range!", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Else
for each myrow in myrng.rows
msgbox myrow.address
next myrow
end if

 

[Bob Phillips]

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select a range!", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Else
MsgBox "First row is " & myRng.Row & ", " & vbNewLine & _
"Last row is " & myRng(myRng.Count).Row
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Nimish]

Dave,

This is a single range, but the key is that I have a macro that reads:

For CopieNumber = 2 To 35
With ActiveSheet
.PageSetup.LeftHeader = Cells(CopieNumber, "A").Value
.Range("A:A").AutoFilter Field:=1,
Criteria1:=Cells(CopieNumber, "A").Value
'Print the sheet
.PrintOut preview:=True
.AutoFilterMode = False
End With
Next CopieNumber
Application.ScreenUpdating = True


for the "CopieNumber" range, I want to sub 2 and 35 for "BEginning of
the selected range" ro "end of the selected range". The range is
continuous.

-Nimish

 

[Dave Peterson]

dim FirstRow as long
dim Lastrow as long

with selection
firstrow = .row
lastrow = .cells(.cells.count).row
end with

for copienumber = firstrow to lastrow