Sort Problem: Unreadable Content

[Virtual Blue]

I am getting this message come up often when I open my workbook:"Excel found unreadable content in 'mybook.xlsm'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."I click Yes and get another dialog box with the following information:"Excel was able to open the file by repairing or removing the unreadable content.Removed Records: Sorting from /xl/worksheets/sheet3.xml part"It tends to vary between 3 different sheets although once the message came up relating to 2 sheets.Can anyone tell me why this would be coming up and what I should be looking for to prevent it from happening?ThanksVB

 

[fshhady]

It's because of the unqualified reference to Range("Table_SQL…..") - it needs a worksheet qualifier.

can you please provided more information. in RogerDodger case, I think he fully reference the range to sort. In his case it's a table.

 

[RoryA]

No he didn't - the part in blue below needs a worksheet reference:

ActiveWorkbook.Worksheets("name").ListObjects("Table_SQL...").Sort.SortFields.Add _
        Key:=Range("Table_SQL…..")

 

[quackdad]

Thank you to all for the "SortFields.Clear" solution - it has worked wonders for me. RogerDodger specifies adding the code after the End With statement. I don't know if I've just been lucky, but adding it between the .Apply and End With seems to work just fine:

    With ActiveWorkbook.Sheets("Calibration").Sort
        .SetRange Range("E3:KQ350")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
        .SortFields.Clear
    End With

 

[RoryA]

You shouldn't need to clear it if you qualify the Setrange part:

   With ActiveWorkbook.Sheets("Calibration").Sort
        .SetRange ActiveWorkbook.Sheets("Calibration").Range("E3:KQ350")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With

 

[quackdad]

You shouldn't need to clear it if you qualify the Setrange part:

I won't pretend to understand (I would assume I'd qualified the range in the With statement), but I'll accept that it is probably good practice to qualify it as you state. The question that comes to mind now is, Is there any reason not to use SortFields.Clear?

 

[RoryA]

(I would assume I'd qualified the range in the With statement)

No - your With statement is holding a reference to the Sort object, not the worksheet.

Is there any reason not to use SortFields.Clear?

Not really - unless you wish to leave the sort settings in place for the user. My personal preference is to leave the settings there so that the user can tell how the data has been sorted.

 

[ymk080690]

i have the same problem, i have a code that sorts 3 sheets, and every time im opening my file a error massage apear "excel found unreadable constants, than the following apear:

Removed Records: Sorting from /xl/worksheets/sheet6.xml part
Removed Records: Sorting from /xl/worksheets/sheet8.xml part
Removed Records: Sorting from /xl/worksheets/sheet11.xml part



i even added the line as you wrote (sort.clear), but no success.

im copying me code here:

ActiveWorkbook.Worksheets("member").AutoFilter.Sort.SortFields.Add Key:= _
Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("member").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("member").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DataBase").AutoFilter.Sort.SortFields.Add Key:= _
Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DataBase").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("DataBase").Sort.SortFields.Clear
MainS.Activate
ActiveWorkbook.Worksheets("main page").AutoFilter.Sort.SortFields.Add Key:= _
Range("A4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("main page").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("main page").Sort.SortFields.Clear

what am i doing wrong??

 

[RoryA]

You need to specify the worksheet for the Range you use as the Key parameter in each sort.

 

[ymk080690]

You need to specify the worksheet for the Range you use as the Key parameter in each sort.

sorry i don't understand.

please explain

what should i add, the range im sorting?!

where should i add it, in the added line or to my original sort code?

if possible please write an example.


thanks

 

[RoryA]

Changes highlighted

ActiveWorkbook.Worksheets("member").AutoFilter.Sort.SortFields.Add Key:= _
ActiveWorkbook.Worksheets("member").Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("member").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ActiveWorkbook.Worksheets("DataBase").AutoFilter.Sort.SortFields.Add Key:= _
ActiveWorkbook.Worksheets("DataBase").Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DataBase").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

MainS.Activate
ActiveWorkbook.Worksheets("main page").AutoFilter.Sort.SortFields.Add Key:= _
ActiveWorkbook.Worksheets("main page").Range("A4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("main page").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With