Avoiding merged rows

[Eric]

If a table has merged rows it is not possible to select a
whole column using VBA.

To get around this I was thinking of creating multiple
ranges by:

1. testing to find the first row without merged cells and
making this the start of the range (m)
2. testing subsequent rows until a row with merged cells
is found (n), then make the previous row (n-1) the end of
the range.
3. repeat steps 1 and 2 to get multiple ranges until the
end of the table

Is this possible? Is there another way?

I think that can I do the testing but am not sure about
the multiple ranges. The changes to each of the ranges
will be the same for all. Should they then be worked one
after the other or can they be concatenated. If so, how?

 

[Peter Hewett]

Hi Eric

I'm not sure why you're trying to do what it is you are doing, so I'll presume you have a
good reason for doing so!?

You method is more of less workable. You could create a collection of range objects. You
would need to test that consecutive merged ranges were actually the same. In other words
two rows may both contain merged cells but each row may be different.

Unless you are going to iterate the table many times the overhead of creating and using
the collection is probably higher than trying to do whatever it is you are trying to do in
a single pass!

HTH + Cheers - Peter