Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colating multi rows of data into single rows - no to pivot tables! | Excel Worksheet Functions | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
"Add/Remove Rows Code" adds rows on grouped sheets, but won't remove rows. | Excel Programming | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions |