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

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

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

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

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

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