Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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. In article , "Sean Farrow" wrote: Thanks for that, I take it the Address property would give the address of the cells at the four courners of the region? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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" wrote in message ... 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. In article , "Sean Farrow" wrote: Thanks for that, I take it the Address property would give the address of the cells at the four courners of the region? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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 In article , "Sean Farrow" wrote: 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" wrote in message ... 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. In article , "Sean Farrow" wrote: Thanks for that, I take it the Address property would give the address of the cells at the four courners of the region? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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) On Sat, 17 Jan 2009 21:52:21 -0000, "Sean Farrow" wrote: 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" wrote in message ... 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. In article , "Sean Farrow" wrote: Thanks for that, I take it the Address property would give the address of the cells at the four courners of the region? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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. "Chip Pearson" wrote in message ... 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) On Sat, 17 Jan 2009 21:52:21 -0000, "Sean Farrow" wrote: 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" wrote in message ... 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. In article , "Sean Farrow" wrote: Thanks for that, I take it the Address property would give the address of the cells at the four courners of the region? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
regions in excel
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) ... In article , "Sean Farrow" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regions of Constant Slope | Excel Discussion (Misc queries) | |||
excel regions | Excel Programming | |||
How do I draw a quadrant with coloured regions in Excel? | Excel Discussion (Misc queries) | |||
Locking rows or Regions in Excel worksheets | Excel Worksheet Functions | |||
How to Combine Data Regions | Excel Programming |