Hi:
two questions:
1. How does excel define aegion of cells?
2. How can I determine the regions of cells in a worksheet and the rane of
cell with any of the defined regions?
Any help apreciated.
Sean

"Region" is not an Excel term but "CurrentRegion" is a block of cells where
each row and each column contains at least one cell which is not empty.
Actually help defines it better as
"The current region is a range bounded by any combination of blank rows and
blank columns."

Dim rCR as range
Set rCR = ActiveSheet.Range("C1").CurrentRegion

msgbox rCR.Address

There is no direct way to list all 'regions' on a sheet other than by
looping non empty cells and finding new CurrentRegion's.

Regards,
Peter T




"Sean Farrow" wrote in message
...
Hi:
two questions:
1. How does excel define aegion of cells?
2. How can I determine the regions of cells in a worksheet and the rane of
cell with any of the defined regions?
Any help apreciated.
Sean

While it is true that there is no way to list all the CurrentRegions,
perhaps this listing of 'cells in use' will suffice for the OP's needs...

Sub GetRegions()
Dim X As Long
Dim Regions As String
Dim FilledCells As Range
Set FilledCells = Union(Selection.SpecialCells(xlCellTypeConstants), _
Selection.SpecialCells(xlCellTypeFormulas))
Regions = Replace(FilledCells.Address, ",", vbLf)
MsgBox Regions
End Sub

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
"Region" is not an Excel term but "CurrentRegion" is a block of cells
where each row and each column contains at least one cell which is not
empty. Actually help defines it better as
"The current region is a range bounded by any combination of blank rows
and blank columns."

Dim rCR as range
Set rCR = ActiveSheet.Range("C1").CurrentRegion

msgbox rCR.Address

There is no direct way to list all 'regions' on a sheet other than by
looping non empty cells and finding new CurrentRegion's.

Regards,
Peter T




"Sean Farrow" wrote in message
...
Hi:
two questions:
1. How does excel define aegion of cells?
2. How can I determine the regions of cells in a worksheet and the rane
of cell with any of the defined regions?
Any help apreciated.
Sean

Not sure about that Rick. As written would error unless the selection
contained both constants and formulas. Also the address will truncate if/as
it approaches 255, though that could be worked around by looping areas.

Regards,
Peter T



"Rick Rothstein" wrote in message
...
While it is true that there is no way to list all the CurrentRegions,
perhaps this listing of 'cells in use' will suffice for the OP's needs...

Sub GetRegions()
Dim X As Long
Dim Regions As String
Dim FilledCells As Range
Set FilledCells = Union(Selection.SpecialCells(xlCellTypeConstants), _
Selection.SpecialCells(xlCellTypeFormulas))
Regions = Replace(FilledCells.Address, ",", vbLf)
MsgBox Regions
End Sub

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
"Region" is not an Excel term but "CurrentRegion" is a block of cells
where each row and each column contains at least one cell which is not
empty. Actually help defines it better as
"The current region is a range bounded by any combination of blank rows
and blank columns."

Dim rCR as range
Set rCR = ActiveSheet.Range("C1").CurrentRegion

msgbox rCR.Address

There is no direct way to list all 'regions' on a sheet other than by
looping non empty cells and finding new CurrentRegion's.

Regards,
Peter T




"Sean Farrow" wrote in message
...
Hi:
two questions:
1. How does excel define aegion of cells?
2. How can I determine the regions of cells in a worksheet and the rane
of cell with any of the defined regions?
Any help apreciated.
Sean