Values repopulate after an umerge operation, possible?

[TheFearofFire]

Hi,

First time poster here and I was hoping someone could explain the apparent anomaly I'm currently experiencing.

The Background.
We have blackbox program that reads a user defined Excel (office 2010 Excel v14) range and converts it to XML and displays it in Word(2010). While the import progam does some error checking its been discovered that it'll import corrupt XML into word when certain 'things' are present in the Excel file.

I intend to write a vba macro/script that accepts a range selection and performs multiple business rule tests upon it and provides me with new worksheet with all the results.

Some examples of the business rules would be

1. If the value in the first cell of row =”xxxxxxx” , are there any values present in cells to the right +1 within the selection?
2. Are there any text boxes with in range?
3. Are there any bold, italic or underline formatting applied to blank cells?
4. Are there merged cells that span over hidden columns?
5. Are merged rows present?
6. Are there merged cells that when unmerged contain multiple values?

I've been guesting this site for a few years and I think I can get most of my answers for the great content already posted.

In a final desperate attempt to discover why an import was failing I decided to unmerge a range (C19:E19), what I didn't expect to see was values (possibly formula) appear for D19 & E19 as well as the value that was displayed when it was merged.

When I deleted the 2 values in D19 & E19 and remerged the (C9:E19) the import to XML was successful.

When I try merging 3 cells in a test worksheet, each with a value, Excel informs me that "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only". OK, and when I unmerge them its true to its word and I only see the upper-left most value.

The Question
Why and what is causing these user worksheets to repopulate the other cells with their values? Is there a vba macro that can find cells that will repopulate when unmerged?


Programs
Excel 2010 (14.0.716.5003)
Word 2010

Postscript
XML & XSLT is my background but needs must and I'm currently debugging my very first MsgBox error in VBA

Thanks
TheFearOfFire

 

[venkat1926]

only a suggestion. unmerge cells format-alignment-text control remove check in merge cells.

 

[Fazza]

Welcome to the forum.

A heads up on merged cells. Many Excel users, me included, avoid merged cells 'like the plague'. Accordingly you will find only a small pool of forumites intersted in the thread. Most will see the word 'unmerge' in the thread title and not read the question. Might it be possible to change your base file so that it never has merged cells??

Due to my unfamiliarity with merged cells, I can't answer your question.

regards