excel regions

[Sean Farrow]

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

 

[Peter T]

"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

 

[Rick Rothstein]

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

 

[Peter T]

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