regions in excel

[Sean Farrow]

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.

 

[Chip Pearson]

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)

 

[Sean Farrow]

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]

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)

 

[JE McGimpsey]

The address property for a multi-cell range returns upper-left and
lower-right cells in the range. You can, of course, easily derive the
upper-right and lower-left addresses.

 

[Sean Farrow]

Hi:
is it possible to obtain the individual top and left addressesof a region,
if not how ae these presented in the address. How would one derive the
bottom and right co-ordinates?
Cheers
Sean.

 

[JE McGimpsey]

One way:

Dim rng As Range
Set rng = ActiveSheet.Range("A1:J10")

'Top Left
Debug.Print rng(1).Address

'Bottom Right
Debug.Print rng(rng.Count).Address

'Top Right
Debug.Print rng(1, rng.Columns.Count).Address

'Bottom Left
Debug.Print rng(rng.Rows.Count, 1).Address

Result:

$A$1
$J$10
$J$1
$A$10

 

[Chip Pearson]

Code like the following will get the top-left and bottom-right cells
of some range.

Dim RR As Range
Dim TopLeftCell As Range
Dim BottomRightCell As Range

Set RR = Range("C3:E5") ' some test range
Set TopLeftCell = RR(1, 1)
Set BottomRightCell = RR(RR.Cells.Count)
Debug.Print "TopLeft: " & TopLeftCell.Address, _
"BottonRight: " & BottomRightCell.Address

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

 

[Sean Farrow]

Chers both, just what I needed.
As a final point onthis: How do I transform an address from all numeric
values, e.g f,3 to letter-number form e.g a3.
Chers
Sean.

 

[JE McGimpsey]

Well, one way would be

addr = Cells(1, 3).Address

But one very rarely needs to use A1-style addresses when one has row and
column numbers.

Using the range object returned by

Cells(1, 3)

directly is far more efficient than

addr = Cells(1, 3).Address
Range(addr) ...