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