dtermine row/column boundaries of selected area

J

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
[email protected]
 
G

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
 
M

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
 
J

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
Click to expand...


That looks cool too!

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


John Keith
[email protected]
 
J

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
Click to expand...

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
[email protected]
 
Top