Referencing Merged Cells in VBA

[Ron Rosenfeld]

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

 

[Ron de Bruin]

Never use Merged cells Is all I can say, only trouble.

Use Center across selection instead

 

[Rick Rothstein]

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.

 

[Ron Rosenfeld]

Never use Merged cells Is all I can say, only trouble.

Use Center across selection instead

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

 

[Ron Rosenfeld]

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

 

[Ron de Bruin]

I never used it like your range, always with cells in the same row.

Sorry, I can't help you

 

[GS]

Ron Rosenfeld used his keyboard to write :

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 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

 

[Ron Rosenfeld]

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

 

[GS]

Thank you for confirming that. I made my "discovery" empirically and it's
good to have confirmation.


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

Sorry for the late reply! You're most welcome!

Ok then, it's good that you found out the ref for E14:E16 is E14.
<IMO>It appears your working with a worksheet that could have had a
better design from the start, but as you say there's not much you can
do about that. Anyway, you will not have any problem if you always ref
the first cell of the merged area. This holds true both in VBA and
in-cell formulas.

Sounds to me like you've got it handled.

regards,
Garry