dtermine row/column boundaries of selected area

[John Keith]

After a user has selected an area on a worksheet I need to have a
macro determine the top and bottom row boundaries and the left and
right column boundaries of the range. How is this accomplished in a
macro?

Thanks


John Keith
(e-mail address removed)

 

[Gary''s Student]

This works well if the Selection is a nice block:

' this routine gives the limits of a range
' first & last row & column
' number of rows and columns
' the address of the range
' the address of the first cell in the range
' the worksheet of the range
' the workbook of the range
' count of cells
Sub range_reporter()
Dim r As Range
Dim s As String
Set r = Selection

nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)

nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)

nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)

numrow = r.Rows.Count
MsgBox ("number of rows " & numrow)

numcol = r.Columns.Count
MsgBox ("number of columns " & numcol)

s = r.Address
MsgBox ("address " & s)

s = r(1).Address
MsgBox ("address of first cell " & s)
MsgBox ("worksheet " & r.Worksheet.Name)

MsgBox ("workbook " & r.Worksheet.Parent.Name)

MsgBox ("item count " & r.Count)
End Sub

 

[Mike H]

Try

Sub Sonic()
FRow = Selection(1).Row
FCol = Selection(1).Column
LRow = FRow + Selection.Rows.Count - 1
LColumn = FCol + Selection.Columns.Count - 1

LRow = Selection(Selection.Cells.Count).Row
LColumn = Selection(Selection.Cells.Count).Column
MsgBox LColumn
MsgBox LRow
MsgBox FCol
MsgBox FRow
End Sub

Mike

 

[John Keith]

This works well if the Selection is a nice block:

Good clarification! It is a "nice" block that I am expecting from the
selection.

Thanks fo rthe quick reply, I'm off and running now.



John Keith
(e-mail address removed)

 

[John Keith]

Try

Sub Sonic()
FRow = Selection(1).Row
FCol = Selection(1).Column
LRow = FRow + Selection.Rows.Count - 1
LColumn = FCol + Selection.Columns.Count - 1

That looks cool too!

I learn so much in this group, I wish I had time to read every thread!


John Keith
(e-mail address removed)

 

[John Keith]

This works well if the Selection is a nice block:

' the worksheet of the range
' the workbook of the range
' count of cells

The comments at the start of the sample code suggested the worksheet,
workbook and cell count can be found but were not inlcuded in the
code. What is the correct structure to get that info?


John Keith
(e-mail address removed)