Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
I am in a situation where I am having to reference merged cells in VBA. The
source of the data is not something I would have any control over or input into. This is a code sample I am using: r1.Offset(0, 1).Value * r1.Offset(0, 2).MergeArea(1, 1).Value r1.offset(0,1) is not a merged cell but r1.offset(0,2) should usually be a merged cell. I suppose it might not be on occasion, but that is not the case yet. Any caveats or other suggestions would be appreciated. In my limited testing, .MergeArea(1,1) returns the data, whether or not the cell is a merged cell. Thanks. --ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
Never use Merged cells Is all I can say, only trouble.
Use Center across selection instead -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron Rosenfeld" wrote in message ... I am in a situation where I am having to reference merged cells in VBA. The source of the data is not something I would have any control over or input into. This is a code sample I am using: r1.Offset(0, 1).Value * r1.Offset(0, 2).MergeArea(1, 1).Value r1.offset(0,1) is not a merged cell but r1.offset(0,2) should usually be a merged cell. I suppose it might not be on occasion, but that is not the case yet. Any caveats or other suggestions would be appreciated. In my limited testing, .MergeArea(1,1) returns the data, whether or not the cell is a merged cell. Thanks. --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
According to the help files for MergeArea, it...
"Returns a Range object that represents the merged range containing the specified cell. If the specified cell isn't in a merged range, this property returns the specified cell" So your code would seem safe to use. I would point out that this part of your expression... MergeArea(1, 1) can be written like this... MergeArea(1) if you want to save a couple of characters. Cells within Ranges can be references as you originally showed (which is really a short cut for using the Cells property) or as a single array of cells where the progression is across the rows, moving down to the next row after reaching the last column in the range. One caveat with the one-dimensional referencing is it does not automatically end at the last cell in the range. If your range were this... Set Rng = Range("D5:H9") then Rng(1) would reference D5, Rng(2) would reference E5, Rng(5) would reference H5, Rng(6) would reference D6, Rng(25) would reference H9 (the last cell in the range; however, Rng(26) would reference D10 and so on. The only way to stop at the last cell is to set a reference of Rng(Rng.Count) as the upper limit of a loop or test for it in an If..Then test. With that said, I find the one-dimensional range referencing to be quite useful at times. -- Rick (MVP - Excel) "Ron Rosenfeld" wrote in message ... I am in a situation where I am having to reference merged cells in VBA. The source of the data is not something I would have any control over or input into. This is a code sample I am using: r1.Offset(0, 1).Value * r1.Offset(0, 2).MergeArea(1, 1).Value r1.offset(0,1) is not a merged cell but r1.offset(0,2) should usually be a merged cell. I suppose it might not be on occasion, but that is not the case yet. Any caveats or other suggestions would be appreciated. In my limited testing, .MergeArea(1,1) returns the data, whether or not the cell is a merged cell. Thanks. --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
On Thu, 6 May 2010 21:41:22 +0200, "Ron de Bruin"
wrote: Never use Merged cells Is all I can say, only trouble. Use Center across selection instead -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm I'm not sure how to apply your advice. For example, in the data, E14:E16 is a merged cell, as is H14:H16. The separate lines of data in F14, F15 and F16 each refer to the data in the merged cells on either side. How do I associate the data in the merged cell, with each of the line items, without using the merged cells? --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
On Thu, 6 May 2010 16:38:58 -0400, "Rick Rothstein"
wrote: According to the help files for MergeArea, it... "Returns a Range object that represents the merged range containing the specified cell. If the specified cell isn't in a merged range, this property returns the specified cell" So your code would seem safe to use. Thanks for that information. It also seems that within a merged area, the only place data can be is in the upper left cell, so that should not be a problem. The data I am summarizing, which has been being presented in a stable fashion for maybe ten years, is now changing; and the initial changes involve my having to reference merged cells in order to extract what I need. So I am having to get involved with merged cells for the first time. I would point out that this part of your expression... MergeArea(1, 1) can be written like this... MergeArea(1) if you want to save a couple of characters. Cells within Ranges can be references as you originally showed (which is really a short cut for using the Cells property) or as a single array of cells where the progression is across the rows, moving down to the next row after reaching the last column in the range. One caveat with the one-dimensional referencing is it does not automatically end at the last cell in the range. If your range were this... Set Rng = Range("D5:H9") then Rng(1) would reference D5, Rng(2) would reference E5, Rng(5) would reference H5, Rng(6) would reference D6, Rng(25) would reference H9 (the last cell in the range; however, Rng(26) would reference D10 and so on. The only way to stop at the last cell is to set a reference of Rng(Rng.Count) as the upper limit of a loop or test for it in an If..Then test. With that said, I find the one-dimensional range referencing to be quite useful at times. I can see where one-dimensional referencing could have some useful applications. Thanks for the hint. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
I never used it like your range, always with cells in the same row.
Sorry, I can't help you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron Rosenfeld" wrote in message ... On Thu, 6 May 2010 21:41:22 +0200, "Ron de Bruin" wrote: Never use Merged cells Is all I can say, only trouble. Use Center across selection instead -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm I'm not sure how to apply your advice. For example, in the data, E14:E16 is a merged cell, as is H14:H16. The separate lines of data in F14, F15 and F16 each refer to the data in the merged cells on either side. How do I associate the data in the merged cell, with each of the line items, without using the merged cells? --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
Ron Rosenfeld used his keyboard to write :
On Thu, 6 May 2010 16:38:58 -0400, "Rick Rothstein" wrote: According to the help files for MergeArea, it... "Returns a Range object that represents the merged range containing the specified cell. If the specified cell isn't in a merged range, this property returns the specified cell" So your code would seem safe to use. Thanks for that information. It also seems that within a merged area, the only place data can be is in the upper left cell, so that should not be a problem. The data I am summarizing, which has been being presented in a stable fashion for maybe ten years, is now changing; and the initial changes involve my having to reference merged cells in order to extract what I need. So I am having to get involved with merged cells for the first time. I would point out that this part of your expression... MergeArea(1, 1) can be written like this... MergeArea(1) if you want to save a couple of characters. Cells within Ranges can be references as you originally showed (which is really a short cut for using the Cells property) or as a single array of cells where the progression is across the rows, moving down to the next row after reaching the last column in the range. One caveat with the one-dimensional referencing is it does not automatically end at the last cell in the range. If your range were this... Set Rng = Range("D5:H9") then Rng(1) would reference D5, Rng(2) would reference E5, Rng(5) would reference H5, Rng(6) would reference D6, Rng(25) would reference H9 (the last cell in the range; however, Rng(26) would reference D10 and so on. The only way to stop at the last cell is to set a reference of Rng(Rng.Count) as the upper limit of a loop or test for it in an If..Then test. With that said, I find the one-dimensional range referencing to be quite useful at times. I can see where one-dimensional referencing could have some useful applications. Thanks for the hint. --ron Hi Ron, Just want to confirm what you already state you just discovered: -the ref address of a merged area is always the first (top left) cell in the group. You're going to have some unexpected results when working with merged cells, depending on what your code is doing with them. What's important about working with DATA in merged cells is the ref address. Also, depending on why the cells are merged, there may be other ways to show data with using Alignment settings. This avoids having to work with merged cells in a single row. (i.e.: Center across selection, Distributed) HTH Garry |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Merged Cells in VBA
On Sun, 09 May 2010 08:54:26 -0400, GS wrote:
Hi Ron, Just want to confirm what you already state you just discovered: -the ref address of a merged area is always the first (top left) cell in the group. You're going to have some unexpected results when working with merged cells, depending on what your code is doing with them. What's important about working with DATA in merged cells is the ref address. Thank you for confirming that. I made my "discovery" empirically and it's good to have confirmation. Also, depending on why the cells are merged, there may be other ways to show data with using Alignment settings. This avoids having to work with merged cells in a single row. (i.e.: Center across selection, Distributed) The Excel sheets are coming from a gov't source over which I have zero control/input/influence/etc. So although there may be other, equally effective methods of displaying the data, I don't really have any choice other than to deal with the data as presented. I suppose I could write a routine to reformat the information, but I doubt that would be any easier than to just handle it directly. For example, in the data, E14:E16 is a merged cell, as is H14:H16. The separate lines of data in F14, F15 and F16 each refer to the data in the merged cells on either side. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with pasting special merged cells to merged cells | Excel Programming | |||
Referencing text in merged cells | Excel Worksheet Functions | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
Referencing a Merged cell | Excel Discussion (Misc queries) | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions |