Excel keep loading my spreadsheet with an error

Question

I keep getting this error when I load my spreadsheet. It makes me repair it, which strips all the validators. The file is saved as an xlsm.

"Excel found unreadable content in '' Do you wnat to recover the contents of this workbook. If you trust the source of this workbook, click yes"

You have to click yes or it won't load. Then I get this error. "Excel was able to open the file by removing or repairing the unreadable content Removed Feature: Data validation from /xl/worksheets/sheet1.xml part"

I haven't a clue about this, and it's really annoying. If anyone has any suggestions, I would be very grateful. Thanks, James

Answer 1

I've gotten that error when I had a long Data Validation list defined in the Data Validation dialog itself (although I can't reproduce it now). If you have a long list in the dialog, try moving the list to a range and then referring to the range.

Answer 2

I just had this same issue with my workbook. I found this link the most helpful - https://stackoverflow.com/a/21483680/3653412.

While the accepted answer would have ultimately addressed the issue for me (rebuilding the workbook), it would have taken a significant amount of time. Clearing the sortfields ultimately fixed the issue for me.

Sub clearSortFields()
Dim ws as worksheet
ThisWorkbook.Activate
For Each ws In Worksheets
    ws.Sort.SortFields.Clear
Next ws
End Sub