Location of Cell with Value in Merge Area

[Guest]

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 A17 Value is in A1

Merged Range is A17 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.

 

[Bernie Deitrick]

bstobart,

Range(any cell in merged area).MergeArea.Cells(1).Value

So, for your example:

Range("C3").MergeArea.Cells(1).Value

 

[Ken Johnson]

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 A17 Value is in A1

Merged Range is A17 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 A17 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 A17 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

 

[Guest]

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.

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 A17 Value is in A1

Merged Range is A17 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 A17 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 A17 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