none
Sorting thousands of rows with VBA - works in 2010, not in 2013? RRS feed

  • Question

  • 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:52 PM
    Thursday, September 25, 2014 1:29 PM

Answers

  • Like Rod your macro works fine for me 2ith 200,000 rows in 2013, this is what I tested with

    Sub SampleData()
    Dim i As Long, j As Long, n As Long
        On Error Resume Next
        ActiveSheet.ListObjects(1).Delete
        On Error GoTo 0
        
        Range("A1:e1") = Array("Date", "Country Code", "Rating", "Segment", "data5")
        n = 200000
        ReDim arr(1 To n, 1 To 5)
        For i = 1 To n
            s = ""
            For j = 1 To 7
                s = s & Chr(Int(Rnd() * 26) + 65)
            Next
            arr(i, 1) = s
            For j = 2 To 4
                arr(i, j) = Int(Rnd() * 10000)
            Next
            arr(i, 5) = i
        Next
    
        Range("A2:E2").Resize(n, 5) = arr
        
    End Sub
    
    Sub testSort()
    Dim sht As Worksheet
    
    Set sht = ActiveSheet    ' ThisWorkbook.Sheets("Csh_Bal")
    
        If sht.ListObjects.Count = 0 Then
            sht.ListObjects.Add(xlSrcRange, sht.Range("A1:G" & sht.[A1000000].End(xlUp).Row), , xlYes).Name = _
            "Table1"
        End If
        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
    End Sub
    
    
    Friday, September 26, 2014 6:35 PM
    Moderator

All replies

  • It probably worked the first time. The second time you either need to reset the listobject's range or add:

    sht.ListObjects("Table1").Unlist

    To convert the table back to a range so the code works again. Worked for 200,000 rows in Excel 2013 SP1 for me.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, September 26, 2014 3:56 AM
  • Thanks Rod. However it doesn't work the first time (the table is definitely created for the first time when it bugs). But I checked the version of Excel 2013 and we don't have SP1 installed (at work). I'll ask if they can do that. We have version Professional Plus 15.0.4623.1000.

    Friday, September 26, 2014 6:45 AM
  • 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.
    Friday, September 26, 2014 4:50 PM
  • Like Rod your macro works fine for me 2ith 200,000 rows in 2013, this is what I tested with

    Sub SampleData()
    Dim i As Long, j As Long, n As Long
        On Error Resume Next
        ActiveSheet.ListObjects(1).Delete
        On Error GoTo 0
        
        Range("A1:e1") = Array("Date", "Country Code", "Rating", "Segment", "data5")
        n = 200000
        ReDim arr(1 To n, 1 To 5)
        For i = 1 To n
            s = ""
            For j = 1 To 7
                s = s & Chr(Int(Rnd() * 26) + 65)
            Next
            arr(i, 1) = s
            For j = 2 To 4
                arr(i, j) = Int(Rnd() * 10000)
            Next
            arr(i, 5) = i
        Next
    
        Range("A2:E2").Resize(n, 5) = arr
        
    End Sub
    
    Sub testSort()
    Dim sht As Worksheet
    
    Set sht = ActiveSheet    ' ThisWorkbook.Sheets("Csh_Bal")
    
        If sht.ListObjects.Count = 0 Then
            sht.ListObjects.Add(xlSrcRange, sht.Range("A1:G" & sht.[A1000000].End(xlUp).Row), , xlYes).Name = _
            "Table1"
        End If
        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
    End Sub
    
    
    Friday, September 26, 2014 6:35 PM
    Moderator