first and last row of range
Hi,
Is there an easy way of capturing the first and last row of a range? I can do it in a complicated way (assigning ActiveWindow.RangeSelection.Address to a string variable and then parsing the string variable to select the numbers within it which give me the first and last row). But I suspect there exists a much simpler way to do it. -- eugene |
first and last row of range
Is the range a single area?
dim myRng as range set myrng = activesheet.range("A9:g77") 'or something with myrng msgbox .row & vblf & .rows(.rows.count).row & vblf & .rows.count end with eugene wrote: Hi, Is there an easy way of capturing the first and last row of a range? I can do it in a complicated way (assigning ActiveWindow.RangeSelection.Address to a string variable and then parsing the string variable to select the numbers within it which give me the first and last row). But I suspect there exists a much simpler way to do it. -- eugene -- Dave Peterson |
first and last row of range
I think I should clarify.
I have selected a range of cells (highlighted). I would like have a macro determine the first and last row of the selection without the need for me to specify anything at all about the range. The only information it has is the fact that cells are selected. -- eugene "eugene" wrote: Hi, Is there an easy way of capturing the first and last row of a range? I can do it in a complicated way (assigning ActiveWindow.RangeSelection.Address to a string variable and then parsing the string variable to select the numbers within it which give me the first and last row). But I suspect there exists a much simpler way to do it. -- eugene |
first and last row of range
Try something like
Sub AAA() Dim LastRow As Long Dim LastCol As Long Dim N As Long N = Selection.Areas.Count ' OR N = 1 LastRow = Selection.Areas(N).Cells(Selection.Areas(N).Rows.C ount, 1).Row LastCol = Selection.Areas(N).Cells(1, Selection.Areas(N).Columns.Count).Column Debug.Print LastRow, LastCol End Sub The result will differ if you have multiple areas selected. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "eugene" wrote in message ... I think I should clarify. I have selected a range of cells (highlighted). I would like have a macro determine the first and last row of the selection without the need for me to specify anything at all about the range. The only information it has is the fact that cells are selected. -- eugene "eugene" wrote: Hi, Is there an easy way of capturing the first and last row of a range? I can do it in a complicated way (assigning ActiveWindow.RangeSelection.Address to a string variable and then parsing the string variable to select the numbers within it which give me the first and last row). But I suspect there exists a much simpler way to do it. -- eugene |
first and last row of range
Dave'
Thanks. I set myrng = Selection (per the clarification I posted a few minutes ago). Your code works perfectly for that as well. -- eugene "Dave Peterson" wrote: Is the range a single area? dim myRng as range set myrng = activesheet.range("A9:g77") 'or something with myrng msgbox .row & vblf & .rows(.rows.count).row & vblf & .rows.count end with eugene wrote: Hi, Is there an easy way of capturing the first and last row of a range? I can do it in a complicated way (assigning ActiveWindow.RangeSelection.Address to a string variable and then parsing the string variable to select the numbers within it which give me the first and last row). But I suspect there exists a much simpler way to do it. -- eugene -- Dave Peterson |
first and last row of range
Chip,
Thanks to you as weill (you guys are great with your quick responses). I'll also try your code. It's always good to know more than one approach to things. -- eugene "Chip Pearson" wrote: Try something like Sub AAA() Dim LastRow As Long Dim LastCol As Long Dim N As Long N = Selection.Areas.Count ' OR N = 1 LastRow = Selection.Areas(N).Cells(Selection.Areas(N).Rows.C ount, 1).Row LastCol = Selection.Areas(N).Cells(1, Selection.Areas(N).Columns.Count).Column Debug.Print LastRow, LastCol End Sub The result will differ if you have multiple areas selected. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "eugene" wrote in message ... I think I should clarify. I have selected a range of cells (highlighted). I would like have a macro determine the first and last row of the selection without the need for me to specify anything at all about the range. The only information it has is the fact that cells are selected. -- eugene "eugene" wrote: Hi, Is there an easy way of capturing the first and last row of a range? I can do it in a complicated way (assigning ActiveWindow.RangeSelection.Address to a string variable and then parsing the string variable to select the numbers within it which give me the first and last row). But I suspect there exists a much simpler way to do it. -- eugene |
All times are GMT +1. The time now is 02:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com