first and last row of range

[eugene]

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.

 

[Dave Peterson]

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]

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.

 

[Chip Pearson]

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.Count, 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]

Dave'

Thanks. I set myrng = Selection (per the clarification I posted a few
minutes ago). Your code works perfectly for that as well.

 

[eugene]

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.