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

Uxorial

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.

Decomposed

Thank you very much for the advice, can you describe what a long Data Validation list would be. I'm generating all of my validators at the moment using vba, as I'd given up using the standard data validators on the excel menu. – Uxorial

It's the kind of validation that creates a dropdown list, something like: ActiveCell.Validation.Add Type:=xlValidateList, Formula1:="tester1,tester2,tester3" ... – Decomposed

Sounds like exactly what I'm using. How would I use a range instead? Do you have some example code please? This is sounding really promising. – Uxorial

ActiveCell.Validation.Add Type:=xlValidateList, Formula1:="=Sheet1!D1:D4" or ActiveCell.Validation.Add Type:=xlValidateList, Formula1:="=rngValidation". In the 2nd "rngValidation" is a named range. If this doesn't make sense, start googling to increase your basic understanding of data validation. – Decomposed

I seem to be stuck in a bind. My selector is a function so I pass it a range. The ranges changes in size so they cannot be named. I previously parsed the range into a list to get it to work. I've tried passing a string to the function s="""=Data!AA1:AA17""" and have Formula1:=s, but that just diplays the string. – Uxorial

You've got too many quotes. Should be s="=Data!AA1:AA17". The best way to figure these types of things out is to record a macro and then modify that code. – Decomposed

Answer 2

I just had this same issue with my workbook. I found this link the most helpful - https://mcmap.net/q/686278/-excel-error-removed-records-sorting-from-xl-worksheets-sheet10-xml-part.

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

Petite

Answer 3

By removing Validation Cells and once again running the code for revalidating on open of workbook solves this issue

Sub RemValidation()
Dim ARows As Variant
Dim i As Double
ARows = Split("C3,C4,C5,C6,C14,C19,C20,C25,F4", ",")

For i = 0 To UBound(ARows)
ThisWorkbook.Sheets("WO").Range(ARows(i)).Validation.Delete
Next

End Sub

Vassili

Answer 4

The issue is out of date, but there may still be those looking for a solution to this problem. When I opened the workbook containing the data validation lists that I created using VBA codes, I was encountering the same problem. On this, I added code snippet to delete data validation lists while closing the workbook :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
   For Each ws In ThisWorkbook.Worksheets
      On Error Resume Next
      ws.Cells.SpecialCells(xlCellTypeAllValidation).Validation.Delete
      On Error GoTo 0
   Next ws
End Sub

As shown in the picture, I have created data validation lists with ordered and unique values using VBA codes and added them to cells in column A.

Source file

Plasticize

Answer 5

This is what I did to fix it, I filled in what I wanted in the list down a column of another sheet in the workbook and then referenced that sheet. I made the sheet name I am reference the list from SheetName (just put your sheet name here) and A2:A19 is referenceing those cells of the reference sheet. This also makes it easier to edit the list if you need to.

Sub Test()
Worksheets("Sheet1").Activate
With rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=SheetName!$A$2:$A$19" 'Replace SheetName and the range with yours
End With
End Sub 

Inexperienced

Answer 6

The easiest way to fix this seems to be simply deleting the validation before exiting the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wkbk As Workbook
Dim wksh1 As Worksheet
Set wkbk = ActiveWorkbook
Set wksh1 = wkbk.Sheets("RTNL Gen")
'Step 1:  Activate the message box and start the check
    Select Case MsgBox("Save and Close?", vbOKCancel, "Rationale Generator"
'Step 2: Cancel button pressed, cancel the close
    Case Is = vbCancel
    Cancel = True
'Step 3: OK button pressed, DELETE VALIDATION, save the workbook and close
    Case Is = vbOK
    wksh1.Range("A2").Validation.Delete
    wkbk.Save
'Step 4: Close your Select Case statement
    End Select
End Sub

Highkey

Answer 7

I got the same error, and I realised my mistake by reading this article, in relevant part:

Due to how Data validations are implemented in Excel/Office Open Xml it is always better to use a shared range rather than adding the validations on each row. For example: if you have 50 000 cells and wants to add a data validation on each one of them try - if feasible - to define a common validation for the cells (i.e. Worksheet.AddListValidation("C1:C50000")) rather than setting them per row.

Thoreau

Could you post a link to the article and quote the text as text, rather than a screenshot (screenshots are kind of hard to read)? – Torino

@RyanM I just added source link. – Thoreau

Thanks! I've edited in a quote of the relevant section as well, so that it's fully readable on the site itself (here are some of the issues with images of text, and links can go stale over time). – Torino