How to deal with merged cells in this case?

[KNTom]

Hi all
I have no experience whatsovever with VBA, other than copy & paste existing code and adapt it a little through lots of googling... However I run into a problem now I cannot solve.
I want to keep track of changes in an excel document, so I use below code. Works fine, except when it has to deal with merged cells. Changes in a single cell are shown as "was changed from" OldVal "to" NewVal. But in case of a merged cell OldVal is never shown, hence I cannot see it was changed and the new input, but not the previous value in the merged cell.
Anyone knows how to deal with this (and please consider me as a simpleton VBA wise)

Thanks a lot!

Please Login or Register  to view this content.

[cytop]

Merged cells are just like shared workbooks - perhaps they're a good theoretical idea but the implementation of that theory left a lot to be desired. The usual advice is not to use merged cells and I can't think of any good reason to do so.

At the risk of being contentious, I'd suggest you redesign the workbook to avoid them. It'll get you over this issue and who knows what problems it'll prevent in the future.


PS:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[gmr4evr1]

I agree with cytop. Merged cells can wreak havoc on a spreadsheet, especially when using code and/or formulas. It's best to stay away from them at all times if possible.

[KNTom]

Apologies; code tags were forgotten. Edited.

But yeah, I share your notion on merged cells, but my colleagues before me not apparantly. I am stuck with it, since it is too time consuming to rebuild hundreds of files to a new design

[cytop]

One reason why OldVal does not contain a value for merged cells...

Please Login or Register  to view this content.

A merged cell will return the number of merged cells... Again, not a solution but a pointer.

[Monimonika]

Found the following VBA code made by a Rick Rochstein in comments at: http://blog.contextures.com/archives...e-excel-cells/

Please Login or Register  to view this content.

What it does is convert any Merged&Centered cells to UnMerged&CenteredAcrossSelection. It skips any Merged Cells that are NOT Centered.
Maybe it can help in fixing your past colleagues' workbooks?

[KNTom]

Thanks for pointing out. But of course, most if not all merged cells are NOT centered

I guess I have to lock myself up for some weeks and redesign all workbooks

[Monimonika]

That sucks

Also at that link I gave, there should be some instructions/code on how to UnMerge all cells at once, but you wouldn't be here if that was all that was needed. Wish you luck in your efforts to clean things up.