I have a large set of data that I want to sort on 4 criteria, 188,610 rows to be exact (and 7 columns). Manually it works without problems. It also works in Excel 2010 with VBA. However, it doesn't work in Excel 2013.

In 2013, the macro blocks on the 'Apply' statement:"run-time error '1004': application-defined or object-defined error". If I reduce the nr of records to 131,000 it works ... Is this a known bug?

I can send you a test file if you like (can't attach it on this forum).

Below my code:

Dim sht As Worksheet

Set sht = ThisWorkbook.Sheets("Csh_Bal")

sht.ListObjects.Add(xlSrcRange, sht.Range("A1:G" & sht.[A1000000].End(xlUp).row), , xlYes).Name = _
        "Table1"
     
With sht.ListObjects("Table1").Sort
        
       .SortFields.Clear
       .SortFields.Add Key:=Range("Table1[Date]"), SortOn:=xlSortOnValues, Order:=xlAscending 
       .SortFields.Add Key:=Range("Table1[Country Code]"), SortOn:=xlSortOnValues, Order:=xlAscending 
       .SortFields.Add Key:=Range("Table1[Rating]"), SortOn:=xlSortOnValues, Order:=xlAscending 
       .SortFields.Add Key:=Range("Table1[Segment]"), SortOn:=xlSortOnValues, Order:=xlAscending 
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply

End With

• Edited by dEX2018 Thursday, September 25, 2014 1:51 PM

Update:

I'm glad to hear that you had find the reason. I copied here and it'll benefit for other communicators.

>>OK, I know the reason now. It's definitely due to the version. It works on my home PC with one of the last versions of Excel.<<

Hi,

I noticed that you have cross post the issue in MSDN forum:

http://social.msdn.microsoft.com/Forums/en-US/0d34b2a6-3fe7-47cb-9c93-e130e63a2141/sorting-thousands-of-rows-with-vba-works-in-2010-not-in-2013?forum=isvvba#a81daf83-390e-4528-b18c-92129a743be0

Have you tried the code that provided by Mr. Peter?

Then, this is the forum to discuss questions and feedback for Microsoft Excel, this issue is more related to Excel Dev, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support

To George & dEX2018:

I've noticed this bug occurs in the Workbook_Open event for Excel 2013.

The SortFields.Clear & SortFields.Apply methods will fail during the Workbook_Open event in Excel 2013. (This bug doesn't occur in earlier versions of such as Excel 2010.)

Conversely, the methods above will work fine any other time.

I would guess that it is caused by tables not being visible on the screen that causes this bug.

Edit: Will keep you posted when I find a workaround.

• Edited by JoelAnthony_2xChampion 9 hours 33 minutes ago