Amount of cells in a range

[ctor]

For starters, I'm stuck with merged cells, can't change it

I need to come up with ways to work around this.
Say I have a merged area of A1:A*, what I would like to know is how many columns are actually used in this merged area.
A1:A4 would return 4, A1:A8 would return 8, A1:C8 would return 8 et cetera.

Now I've played a bit with MergeArea.Address and although it returns me the correct range, I'm stuck with actually using it further.

Any ideas?

[Jack in the UK]

Once you know

example Cell A is covering A1 - A3 and your returned value = 3

Then waht do you want to do

Remember the merged cell will be known as A1 and ccells A2 & A3 will be gone for now

[Jack in the UK]

Please Login or Register  to view this content.

[royUK]

You will find that all experienced VBA coders will tell you to avoid Merged Cells like the plague.

A range of merged cells becomes one cell & one Column

[ctor]

MergeArea.Count was exactly what I was looking for
Cheers...




If I was able to avoid them, I would, they're the only, single, reason this is taking long. Experienced or not, in some situations you can't avoid working with stuff you'd rather not work with.
These sheets are generated by some piece of software, over which I have no control. It just happens to contain alot of these merged areas, I have to program my way around it and it's not pretty.

You can't control other peoples' software and I can't go to the client telling him to **** off with his merged cells. That's just not how the world works
Especially as I need to pay my mortgage fees every month and feed myself

What I want to do with it, and this is ugly, is I copy values from some sheet to a sheet displaying information to the user, this display sheet is riddled with merged cells. But the data sheet is 'normal', so I have to copy stuff from the datasheet to the display sheet, but that would give lots of errors because of the merged cells, so i temporary disable the merged cells, paste the data, remerge them (ï've to apply some formatting later) so as not to toy with the original template.
It's a 2 week project, so I'm bound to get a high blood pressure the coming days

This is the code (at this moment), it's my first attempt at Excel and/or VBA, so it's probably bad, but it's a work in progress and more of a concept
Feel free to laugh at its ugliness.

Please Login or Register  to view this content.

[PButchart]

Ctor, One thing that I'd suggest for you is to make a copy of the source page, use it as your working page and delete it when you're finished. That eliminates the requirement of restoring all of the merged cells.