The Address property will return the address of the range of cells
that make up the region, not including the blank cells along the
"edges" of the region. You can use simple code like

Sub AAA()
MsgBox ActiveCell.CurrentRegion.Address
End Sub

to display the address of the CurrentRegion of the currently selected
cell.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Sat, 17 Jan 2009 19:54:19 -0000, "Sean Farrow"
wrote:

Hi Chip:
Thanks for that, I take it the Address property would give the address of
the cells at the four courners of the region?
Cheers
Sean.
"Chip Pearson" wrote in message
.. .
There is no object named "Region". The only thing in Excel related to
a "Region" is the CurrentRegion property of a Range. The CurrentRegion
is a rectangular range which contains the specified cell and is
bounded on all four sides entirely by blank cells (or the edge of the
worksheet). E.g.,

Debug.Print ActiveCell.CurrentRegion.Address

I don't believe there is any way to get all of the regions that define
all the data on a worksheet. You could do it with a brute force loop,
looking at the CurrentRegion for every cell in the UsedRange, but that
could get very large and slow for a worksheet with a lot of data.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 17 Jan 2009 19:05:35 -0000, "Sean Farrow"
wrote:

Hi:
Two questions:
What is technically defined as an excel region?
2. Is there a way with the object model to detect the different regions in
a
spreadsheet.
Cheers
Sean.