Find Merged Cells VBA

[Admin]

Hi Rick,

I thought about the findformat method. But I think findformat was introduced in XL 2002/2003 (not sure which one).

So I thought it would be better a function whch works on all versions(not sure about XL 97 though)

Another point, if you pass a string it must be a range address, otherwise it would throw error.

I did a small test on A1:K10000 with 1169 merged areas, and the average time taken is as follows:

Ricks - 5 secs
Mine - 7 secs

Anyway thanks for posting an alternative (I would say native) method.

[Rick Rothstein]

I thought about the findformat method. But I think findformat was introduced in XL 2002/2003 (not sure which one).

So I thought it would be better a function whch works on all versions(not sure about XL 97 though)

I only have XL2003 and XL2007 installed, plus I only came to Excel maybe 5 years ago, so I am unaware of any limitation on versions of Excel prior to XL2003. Besides, why worry about earlier versions of Excle... everyone has moved up to at least XL2003 by now, right?

Another point, if you pass a string it must be a range address, otherwise it would throw error.

Isn't that what functions normally do when they get passed invalid arguments? I would think it is up to the programmer to either filter out improper argument values before the function is called or to handle errors generated by the function after they are produced. Did you have something different in mind for what my function should do if the passed in String value was not a range address?

I did a small test on A1:K10000 with 1169 merged areas, and the average time taken is as follows:

Ricks - 5 secs
Mine - 7 secs

While I would always expect my function to be faster than what you posted, the percentage difference between them is highly dependent on the number of merged cells involved. Both of our routines repeatedly use the Union function for each merged cell in the range. You test with 1169 merged cells means 1169 Union operations... the Union method gets progressively slower with each new member added to it and I think 1169 of them is enough to slow things down noticeably and, as a result, hide the true difference between our codes. What times do you get if you repeat that test with, say, 5 merged areas?