excel regions
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 |
excel regions
"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 |
excel regions
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 |
excel regions
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 |
All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com