Excel 2007 : Sorting - Message re Removed Records

**Richard Buttrey** · 10-24-2008, 05:22 AM

In 2007 I have a sorting procedure. After I save and close the file and re-open it I get the following message

"Excel found undreadable content in ... Do you wish to recover contents ..."

I reply Yes. Then I get:

Removed Records: Sorting from /xl/worksheets/sheet2.xml part

If I then immediately save/close the file and re-open it without first running the sort macro, then everything is fine. There is no warning message. After I sort and do a save/close open the message returns - so clearly there's something in the sorting procedure that causes this.

No records are lost from the sorted range despite what the message might imply.

I don't know whether this is a factor since it seems un-related. But after I open the file and accept the warning message and before running the sort macro, if I then immediately click the 'Save' icon I'm presented with the File Save As dialog box rather than the file just being resaved. It appear that Excel is thinking this is a new file.

Any ideas??

Usual TIA

**Andy Pope** · 10-24-2008, 05:33 AM

Can you copy the data and macro into a new workbook and re-create the problems?

**Richard Buttrey** · 10-24-2008, 09:52 AM

Thanks Andy,
I'll try that as well.

I've been selectively deleting sheets to see if I can identify where the source of the problem may lie.

The original data was copied in from another workbook, and it has been summarised, pivoted, and filtered before it has ended up in this particular sort range, which no doubt has complicate the matter somewhat.

I've also noticed that I was getting a message when pivoting a table referring to an external link, even though there don't appear to be any links in the workbook. Not sure whether that might be a contributing factor.

Needless to say this is XL 2007 which seems somewhat fussy about several aspects which I don't remember being a problem in earlier versions.

Regards

**Richard Buttrey** · 10-25-2008, 02:40 PM

Hi Andy - and for anyone else who's interested.

I finally tracked down this problem.
The sorting macro was using a line of code :

.Sort.SortFields.Add

part of the macro is a loop which processes this line twice, and it was adding a second occurrence of the same sort key. Seemingly the fact that there are two instances of the same key causes the odd warning message I was getting.

I added an initial additional line of code:

.Sort.SortFields.Clear

and this has sorted the problem.

Rgds

**Andy Pope** · 10-26-2008, 07:09 AM

Thanks for posting back your solution.

**Deschutes** · 05-28-2013, 11:51 AM

Originally Posted by Richard Buttrey

Hi Andy - and for anyone else who's interested.

I finally tracked down this problem.
The sorting macro was using a line of code :

.Sort.SortFields.Add

part of the macro is a loop which processes this line twice, and it was adding a second occurrence of the same sort key. Seemingly the fact that there are two instances of the same key causes the odd warning message I was getting.

I added an initial additional line of code:

.Sort.SortFields.Clear

and this has sorted the problem.

Rgds

Ive attempted to place the .Sort.SortFields.Clear in several places and still error out. Can anybody help?

Sheets("PLORD").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$2:$E$200").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveWorkbook.Worksheets("PLORD").Sort.SortFields.Add Key:=Range("B3:B201") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("PLORD").Sort.SortFields.Add Key:=Range("C3:C201") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("PLORD").Sort
        .SetRange Range("A2:AE201")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

**sspatriots** · 11-24-2013, 02:09 PM

Try and add the following just after the 'End With'

End With

ActiveWorkbook.Worksheets("PLORD").Sort.SortFields.Clear

**acowper** · 07-26-2013, 03:52 AM

Lifesaver, thanks

**DBExhale** · 02-21-2012, 05:54 AM

Many thanks for posting this solution.
Saved me hours of headaches.

**freesa** · 07-04-2012, 01:29 AM

Hello Andy!

This maybe is the answer I've been looking for. Pardon me, but where should I put the line ".Sort.SortFields.Clear"?

Thanks!

Joden

**Leonbb** · 08-31-2012, 09:35 AM

Still relevant after almost 4 years, this line fixed the error in Excel 2010 for me as well.
I've put this in the next line after the .Sort.SortFields.Add bit, and gone is the error message

Thanks!

**abelvch** · 09-06-2012, 07:08 PM

Thanks ! It works !!

**Renns** · 10-31-2012, 01:37 PM

Oh my god, thank you thank you thank you!

This was blowing my mind for 2 days. My sort funtion (which is used by many triggers throughout my program) was receiving the sort range, 2 sort keys and 2 sort orders. On only ONE (of the many) instances of this fuction being called, the SortKey1 and SortKey2 that were being passed in were identical. I changed one of them, and now I no longer receive the dumb error when reopening the file.

Thanks so much!

**chuck.bowman** · 11-29-2012, 09:45 PM

And 3 months later it still works! Thanks for the great advice folks!

**davesky** · 05-28-2013, 09:27 AM

I also had the same problem. Just changed the sortkey2 so it was different from sortkey1
and it works fine.

Thanks so much!!

**eeijas** · 01-21-2014, 07:37 AM

the solution still doesn't work for me

**dpelan** · 05-19-2014, 02:34 PM

Thanks for posting the solution. Saved me a lot of time and trouble!

Darrell

**saintpaulie** · 10-22-2014, 10:59 AM

That's helped me out a lot. Thank you!

**AmirSoft** · 09-18-2015, 02:11 AM

Hi Andy - and for anyone else who's interested.

I finally tracked down this problem.
The sorting macro was using a line of code :
Select Code copy to clipboard
.Sort.SortFields.Add
part of the macro is a loop which processes this line twice, and it was adding a second occurrence of the same sort key. Seemingly the fact that there are two instances of the same key causes the odd warning message I was getting.

I added an initial additional line of code:

Select Code copy to clipboard
.Sort.SortFields.Clear
and this has sorted the problem.

Rgds

Please tell me where to put this code. and can I solve the problem.
I saved the in .xlsm and .xlsb but both have this error.
If I transfer data to new sheet then is it possible the new work book also show this message.

And every time I open my file excel ask me to repair the file. Why?

Please guide me

**A8_Tony** · 03-03-2016, 04:37 AM

Thank you, thank you, thank you!!!!! Still relevant in Excel 2016!!!!!!

**6StringJazzer** · 10-14-2017, 10:33 AM

Yet another a$$ saved by this advice! Thanks Richard!

**Richard Buttrey** · 10-30-2017, 01:21 PM

Originally Posted by 6StringJazzer

Yet another a$$ saved by this advice! Thanks Richard!

Thanks Jeff - and for the rep.

I'm only too pleased that it's been of benefit to others over the last nine years.

It's also encouraging to see that users here are using the search facility to find these things.

**perrush** · 01-10-2018, 11:29 AM

Hi all,

I have the same issue. I have several sheets with sorting, but the error is referring to 'tabel 1'. And sheet "LTSB" is also the one who is open on saving. so I suppose the problem is with this sheet.

I did try to save as .xlsb but no solution.

I put in the extra code mentioned in this thread, but I still keep getting this error. Anyone any suggestion ?

    ActiveWorkbook.Worksheets("LTSB").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Factory]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
        
    ActiveWorkbook.Worksheets("LTSB").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[ProductId]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
        
    ActiveWorkbook.Worksheets("LTSB").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[VERID]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
     
    ActiveWorkbook.Worksheets("LTSB").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[start datum]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
        
    ActiveWorkbook.Worksheets("LTSB").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[start uur]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
        
        
   With ActiveWorkbook.Worksheets("LTSB").ListObjects("Tabel1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ActiveWorkbook.Worksheets("LTSB").Sort.SortFields.Clear

**6StringJazzer** · 01-10-2018, 11:36 AM

perrush, welcome to the Forum. As a new member, please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member.

We also have a rule against hijacking, which is asking a new question in an existing thread. In this case I'll give you the benefit of the doubt since your question is directly related to advice given here. You must put your line of code

ActiveWorkbook.Worksheets("LTSB").Sort.SortFields.Clear

at the beginning, not the end.

**perrush** · 01-10-2018, 11:49 AM

Hi 6StringJazzer, tnx for correcting my post. Will add the code-tag next time.

I'm a little confused with your remark about adding the Sort.SortFields.Clear at the beginning and not at the end. In 2 different posts it is stated that you should add it after the 'end with'. I'm not sure what to do know.

Maybe this has something to do with my limited capabilities in excel.

But I still get this error and looking for a solution

**6StringJazzer** · 01-10-2018, 11:58 AM

Originally Posted by perrush

I'm a little confused with your remark about adding the Sort.SortFields.Clear at the beginning and not at the end. In 2 different posts it is stated that you should add it after the 'end with'.

Rather than going back and forth in this thread, it will be much faster if you just try my suggestion and see if it works. Your code is different than the code that the older answer applied to, which is why we discourage asking new questions in existing threads.

If you try my suggestion and it does not work please start a new thread and attach your file to it.

**brypablo** · 10-26-2018, 04:12 AM

Thank you!

**Jason M** · 10-02-2019, 08:48 AM

Beethoven's 9th!!! Thanks for this...

**amazingg64** · 06-30-2020, 02:22 PM

Still works! Even Corona can't stop us.

With .Sort
        .SortFields.Add Key:=sht_Master.Cells(1, Date_col), Order:=xlAscending
        .SetRange sht_Master.UsedRange
        .Header = xlYes
        .Apply
        .SortFields.Clear
    End With

**Hubbub** · 09-01-2020, 06:07 PM

Fantastic, Thank you!!!

**nminter** · 09-19-2021, 06:56 AM

This solution just keeps on giving! Thank you

**xykarelxy** · 11-19-2021, 02:32 AM

Also helped me. Thanks a lot!

Excel 2007 : Sorting - Message re Removed Records

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Sorting - Message re Removed Records

Re: Sorting - Message re Removed Records

Re: Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Re: Excel 2007 : Sorting - Message re Removed Records

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions