0

i'm using this code for sorting (checked for Excel 2010/2013):

    Worksheets("Tabelle4(1)").Activate
ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "W2:W51"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

I loop over a sheet 70 times with different values and sort and export them as a pdf. Everything works fine, but after approximately 30 times I get an error 1004. If I start the loop at this point 30 again it works fine. The problem doesn't seem to do with the values. Is there a buffer inside of excel, which I've to clear from time to time?

user2405095
  • 113
  • 4
  • 16
  • How do you pass the 70 values to this line? I'm not aware of any such buffer. Looks more like you are trying to sort with invalid parameters. Can you reproduce it? Does it stop at the same value each time? – nicolaus-hee Apr 27 '15 at 13:19
  • I get the values from an SAP Add-In. It doesn't seems to be to do with the parameters. "Does it stop at the same value each time?" No, there are different values. But it fails always between 30 and 35 rounds, at this point of coding. – user2405095 Apr 27 '15 at 13:27
  • I have to start excel new again, otherwise excel would stop with the same error again, even there are other values. – user2405095 Apr 27 '15 at 13:32
  • If I start excel again from the error point, I've no trouble. – user2405095 Apr 27 '15 at 13:34
  • How about this: is it possible you have more than one worksheet and keep jumping back and forth while the macro is running? Your macro references `ActiveWorksheet` which could be changing while the macro is running. After restarting Excel it is working which could be because the other sheets were closed but not reopened after you restarted Excel. – nicolaus-hee Apr 27 '15 at 13:37
  • Or is there a time limit? It runs for almost an hour, befor it crashes – user2405095 Apr 27 '15 at 13:37
  • Wow, one hour? My bet is still on the `ActiveWorksheet` as the culprit unless you are not touching your computer the whole time. You may find this article helpful: [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) (it also comments on `ActiveWorksheet`). – nicolaus-hee Apr 27 '15 at 13:43
  • @nhee: thanks for your reply. I don't think so, because I'm activating the sheet directly for sorting. And if you would be right, it should work, when I'm debugging it. But even if I debug the macro and have the sheet in front of mine, it fails. – user2405095 Apr 27 '15 at 13:44
  • @nhee: i don't belive that is a coincidence that the sheet is always not active at this point of loop. – user2405095 Apr 27 '15 at 13:48

2 Answers2

0

You should clear your Sort fields from time to time indeed, because they just accumulate and it'll be harder for you to prioritize them.

And just don't use Activate , nor Select which is even worse, so just combine Sheets().Activate and ActiveSheet.Sort to Sheets().Sort , your code will be much more efficient.

This should help you :

With Worksheets("Tabelle4(1)")
        .Sort.SortFields.Add _
            Key:=Range("W2:W51"), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal

        .Sort.Orientation = xlTopToBottom
        .Sort.Apply
        'here is your export

        .Sort.SortFields.Clear
End With
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • Hi R3uK, it works, perfectly! I saw this clear-Methode before, but I didn't give it a chance. :) However your answer convinced me to check it out. – user2405095 Apr 28 '15 at 06:24
  • The different is, I put this code on the top of my coding: ActiveWorkbook.Worksheets("Tabelle4(1)").Sort.SortFields.Clear – user2405095 Apr 28 '15 at 06:36
  • "don't use Activate , nor Select" For this project performance is not relevant, thats why I don't want to change my coding. It works fine and thats enough! But for the future I'll keep it in mind. :) – user2405095 Apr 28 '15 at 06:40
  • Mkay... If you want to wait longer that your code has finished his work, keep it as it is. You don't have to upvote the answer, but you can accept it : just under the up and down vote, there is a tick that will turn green when you click on it! Enjoy – R3uK Apr 28 '15 at 07:26
0
    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range(Cells(startRow, 1), Cells(endRow, 3))
        'startRow=2, endRow=18
        'Sort Table Date Decending Order
    ws.Sort.SortFields.Clear
    With ws
        .Sort.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With

'This works but does not sort the data descending.

twmcc
  • 1