Sort Problem: Unreadable Content

Virtual Blue

New Member
Joined
Dec 7, 2009
Messages
3
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
No he didn't - the part in blue below needs a worksheet reference:
Rich (BB code):
ActiveWorkbook.Worksheets("name").ListObjects("Table_SQL...").Sort.SortFields.Add _
        Key:=Range("Table_SQL…..")
 
Upvote 0
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:
Code:
    With ActiveWorkbook.Sheets("Calibration").Sort
        .SetRange Range("E3:KQ350")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
        .SortFields.Clear
    End With
 
Upvote 0
You shouldn't need to clear it if you qualify the Setrange part:
Code:
   With ActiveWorkbook.Sheets("Calibration").Sort
        .SetRange ActiveWorkbook.Sheets("Calibration").Range("E3:KQ350")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
 
Upvote 0
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?
 
Upvote 0
(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.
 
Upvote 0
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??
 
Upvote 0
You need to specify the worksheet for the Range you use as the Key parameter in each sort.
 
Upvote 0
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
 
Upvote 0
Changes highlighted
Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,226,227
Messages
6,189,753
Members
453,567
Latest member
kentbarbie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top