Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Location of Cell with Value in Merge Area
If I have a range object consisting of only a single mergedarea in VBA and
want to determine which cell is the one holding the value, how can I do that? Two examples: Merged Range is A1:D7 Value is in A1 Merged Range is A1:D7 Value is i n D7 [I believe that the value of a merged range can only be in the upper right cell or the lower left cell. The above examples are for illustration only.] In a general sense, how can I determine the location of the cell with the value? Note: I won't know in advance whether the cell with the value is populated or blank. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Location of Cell with Value in Merge Area
bstobart,
Range(any cell in merged area).MergeArea.Cells(1).Value So, for your example: Range("C3").MergeArea.Cells(1).Value -- HTH, Bernie MS Excel MVP "bstobart" wrote in message ... If I have a range object consisting of only a single mergedarea in VBA and want to determine which cell is the one holding the value, how can I do that? Two examples: Merged Range is A1:D7 Value is in A1 Merged Range is A1:D7 Value is i n D7 [I believe that the value of a merged range can only be in the upper right cell or the lower left cell. The above examples are for illustration only.] In a general sense, how can I determine the location of the cell with the value? Note: I won't know in advance whether the cell with the value is populated or blank. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Location of Cell with Value in Merge Area
On Sep 13, 10:38 pm, bstobart
wrote: If I have a range object consisting of only a single mergedarea in VBA and want to determine which cell is the one holding the value, how can I do that? Two examples: Merged Range is A1:D7 Value is in A1 Merged Range is A1:D7 Value is i n D7 [I believe that the value of a merged range can only be in the upper right cell or the lower left cell. The above examples are for illustration only.] In a general sense, how can I determine the location of the cell with the value? Note: I won't know in advance whether the cell with the value is populated or blank. It looks like that information is lost once the cells are merged. If the original unmerged range held multiple values it is only the upper leftmost value that is retained and assigned to the top left cell of the merged range (even though it might not appear in the top left corner of the merged range). If the Merged range is A1:D7 and the value was in D7 before merging (all other cells blank), then after merging A1 = the value originally in D7, all the other cells in A1:D7 are blank (ie D7 no longer holds the value and there seems to be no way of determining that it was the original cell holding the value). Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Location of Cell with Value in Merge Area
Thanks Bernie and Ken.
I was foolish enough to doubt Bernie's answer because I expected the reference to Cell(1) to only work when the value was originally in the upper left most cell (ie Cell(1)), but as Ken noted, I've also confirmed with some experimentation that when you merge cells, if the value was originally in the lower right or lower left hand corner, that value is moved to Cell(1) when the cells are merged. This is interesting and unexpected behavior. [Ken, In my case I'm not concerned with scenario of values in the other cells in the selected range before the merge. When my macro runs, the cells are always already merged. I just need to be able to identify the location of the cell with the value.] Bernie provided exactly the answer that I needed. Thanks again. "Ken Johnson" wrote: On Sep 13, 10:38 pm, bstobart wrote: If I have a range object consisting of only a single mergedarea in VBA and want to determine which cell is the one holding the value, how can I do that? Two examples: Merged Range is A1:D7 Value is in A1 Merged Range is A1:D7 Value is i n D7 [I believe that the value of a merged range can only be in the upper right cell or the lower left cell. The above examples are for illustration only.] In a general sense, how can I determine the location of the cell with the value? Note: I won't know in advance whether the cell with the value is populated or blank. It looks like that information is lost once the cells are merged. If the original unmerged range held multiple values it is only the upper leftmost value that is retained and assigned to the top left cell of the merged range (even though it might not appear in the top left corner of the merged range). If the Merged range is A1:D7 and the value was in D7 before merging (all other cells blank), then after merging A1 = the value originally in D7, all the other cells in A1:D7 are blank (ie D7 no longer holds the value and there seems to be no way of determining that it was the original cell holding the value). Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - save to current location vs excel default location | Excel Discussion (Misc queries) | |||
How to shift the cell based on the location of other cell? | Excel Programming | |||
automatic offset cell location when there is a value in a selected cell. | Excel Worksheet Functions | |||
How to merge all selected areas into one area | Excel Programming | |||
only last cell on page to have bottom border (cell area outline) | Excel Worksheet Functions |