First and last rows

[Francis Hookham]

I cannot see how to get started with this:



Having manually selected a range



typical might be B1025 or A77:A254



at the beginning of a macro, I need to set variables to record the first and
last rows.



So, in the case of A77:A254



FirstRow = 77

LastRow = 245



Please show me how to do this.



Francis Hookham

 

[Ron Coderre]

Since it's conceivable that you could have more than
one area selected, and the last area selected may be
above one of the other areas...

This code returns the first row of the
highest area and the last row of the lowest area:

Sub FirstLastSelRow()
Dim rArea As Range
Dim iBullpen
Dim iLastSelRow
Dim iFirstSelRow

iFirstSelRow = 10 ^ 10
iLastSelRow = 0
For Each rArea In Selection.Areas
iBullpen = rArea.Row
If iBullpen < iFirstSelRow Then
iFirstSelRow = iBullpen
End If

iBullpen = rArea.Row + rArea.Rows.Count - 1
If iBullpen > iLastSelRow Then
iLastSelRow = iBullpen
End If
Next rArea
MsgBox "FirstRow: " & iFirstSelRow & " LastRow: " & iLastSelRow
End Sub

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Francis Hookham]

Thanks Ron - it will be only one range so can it be simpler?

Francis

 

[Ron Coderre]

Sure...try this:

Sub FirstLastSelRow_1area()
Dim iLastSelRow
Dim iFirstSelRow

With Selection
iFirstSelRow = .Row
iLastSelRow = iFirstSelRow + .Rows.Count - 1
End With
MsgBox "FirstRow: " & iFirstSelRow & " LastRow: " & iLastSelRow
End Sub

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Francis Hookham]

Exactly what I want - thank you very much.
It will be useful now and I know it will be for other occasions.

Francis

 

[Ron Coderre]

You're welcome, Francis.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)