Excel Data Validations corrupt worksheets (when copying?)

Question

Edit:

I further looked into this issue and have now a minimal file in which I can reproduce the error/bug. All Add-Ins are deactivated and I use only 4 lines of VBA-Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Tabelle15.Range(Target, Target.Offset(1, 0)).EntireRow.Copy
        Target.Offset(2, 0).EntireRow.Insert
End Sub

The file has 2 sheets with formatting, data validations, outlines (3 levels) and some data on it.

What I do to corrupt the files:

• Open the file
• Click 3 times on Outline-Level 2 (--> hiding some rows)
• Click 3 times on Outline-Level 3 (--> unhiding the rows)
• Doubleclick on a cell to copy two rows via the VBA-Code
• Close the file
• Repeat until file is corrupt (sometimes it needs 10 or more loops, sometimes the file is corrupt after 1 loop)

The sheet in the file which gets corrupted doesn't have any code in it and is not activated in these loops. Here is a before and after of the corrupted data validation:

Before corruption:

<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="D11 D13 D15 D9">

After corruption:

<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="D11:A11 1:A90 A16384:D4294967295 D9">

I have still no idea why this happens. It would be interesting if anyone has the same issue or knows of a workaround so the corruption doesnt happen. (I tried the obvious Application.Screenupdating, Application.Wait,... )

End of Edit ---

Unfortunately I cannot say when this problem occurs, because I only get the resulting corrupt workbooks.

We have written a COM-AddIn for Excel in VB.net which does all sort of things (i.e. copying cells and sheets in specific structures, data connections to an SQL-DB, saving the workbook ...)

Now I get some workbooks from users of the AddIn which are corrupted. The problem is the users are working with the files and don't have a problem. Only after saving, closing and reopening the file Excel shows the question if the file should be repaired. Depending on the file type (we use *.xlsx and *.xlsb) Excel "repairs" the file and removes all formating from 1 sheet - in *.xlsb it doesn't repair and open at all.

After going through the xml-Sheet files I found that the corruption occurs in the dataValidation-Tags. Not in one specific data-validation sometimes in one sometimes in another.

The corrupted dataValidation-tags look like this:

<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="S26:26 1:A523 A16384:S4294967295 S32"><formula1>"Item1,Item2,Item3"</formula1></dataValidation>

Repairing the data validation or the files is not the problem. By now I can recover my workbooks just fine. My main problem is that I can't find any lead to where this problem comes from.

The most operations which happen on the sheets are copying and inserting (not pasting) of entire rows. On one sheet which gets corrupted sometimes, there is only "copying from" and no pasting or inserting on the sheet. It also seems that general performance of the file impacts the frequency of the problem occuring.

Since I can't give any code where this corruption occurs I don't expect to find a solution here, but perhaps someone had a similar problem and has a hint which shows me the right direction.

Additional infos: The problem occurs on different machines. I did not manage to reproduce the corruption process. As far as I know the problem only occured on Excel 2016 but I can't rule out that earlier versions corrupt the files just the same. I checked (at least on some machines) that all updates for Office and Windows are installed. The formulas in the data validations have (way) less than 255 characters. On the corrupted sheets there is no data connection to a database.

Any idea or hint is appreciated!