Excel 2007 : Sorting - Message re Removed Records

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

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

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

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

and this has sorted the problem.

Rgds

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

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

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!

Thanks ! It works !!

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!

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

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

Thanks so much!!

Lifesaver, thanks

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


End With

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

the solution still doesn't work for me

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

Darrell

Lifesaver! Thanks a lot.

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

7 years after the original post and this just saved my life!!

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

Originally Posted by sspatriots

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


End With

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

This worked, thank you.

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

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.

Thank you!

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

Still works! Even Corona can't stop us.

Please Login or Register  to view this content.

Fantastic, Thank you!!!

This solution just keeps on giving! Thank you

Also helped me. Thanks a lot!