ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   First and last rows (https://www.excelbanter.com/excel-programming/408723-first-last-rows.html)

Francis Hookham
First and last rows
 
I cannot see how to get started with this:



Having manually selected a range



typical might be B10:D25 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
First and last rows
 
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" wrote in message
...
I cannot see how to get started with this:



Having manually selected a range



typical might be B10:D25 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

Francis Hookham
First and last rows
 
Thanks Ron - it will be only one range so can it be simpler?

Francis


"Ron Coderre" wrote in message
...
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" wrote in message
...
I cannot see how to get started with this:



Having manually selected a range



typical might be B10:D25 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
First and last rows
 
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" wrote in message
...
Thanks Ron - it will be only one range so can it be simpler?

Francis


"Ron Coderre" wrote in message
...
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" wrote in message
...
I cannot see how to get started with this:



Having manually selected a range



typical might be B10:D25 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

Francis Hookham
First and last rows
 
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" wrote in message
...
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" wrote in message
...
Thanks Ron - it will be only one range so can it be simpler?

Francis


"Ron Coderre" wrote in message
...
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" wrote in message
...
I cannot see how to get started with this:



Having manually selected a range



typical might be B10:D25 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
First and last rows
 
You're welcome, Francis.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)

"Francis Hookham" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thanks Ron - it will be only one range so can it be simpler?

Francis


"Ron Coderre" wrote in message
...
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" wrote in message
...
I cannot see how to get started with this:



Having manually selected a range



typical might be B10:D25 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


All times are GMT +1. The time now is 11:48 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com