Using Sort function in macro crashes

I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message:

Run-time error ‘1004’:
Sort method of Range class failed

When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?

The code for the first sort looks like this:

Sub DSort1()
'
' DSort1 Macro
' Macro recorded 6/3/2009 by CCE User
'

'
    Range("A1:AI76").Sort Key1:=Range("M2"), Order1:=xlAscending, Key2:=Range _
        ("K2"), Order2:=xlAscending, Key3:=Range("N2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortTextAsNumbers, DataOption2:= _
        xlSortTextAsNumbers, DataOption3:=xlSortNormal
End Sub

Any help here would be greatly appreciated.

Welcome to the forum, SFA. There are several ways to do what you ask.

Is your "table" really only column A as demonstrated in the macro? It appears you are sorting on multiple "keys" but only one column. That seems odd to me.

What is the width of the table? Does that change? Is it the # of rows you want to dynamically select?

Perhaps it would be easier for us to picture if you uploaded a couple of sample datasets showing the table in a couple of different forms...

Click on GO ADVANCED and use the paperclip icon to upload some some sample data, point out the real keys you want to use in each and how they were identified. Perhaps even before/after examples.

Let's try it this way:

Sub RouteSort()
Dim LR as Long
LR = Range("A" & Rows.Count).End(xlUp).Row
    
Range("A2:AI" & LR).Sort Key1:=Range("M3"), Order1:=xlAscending, Key2:=Range("K3"), _
    Order2:=xlAscending, Key3:=Range("N3"), Order3:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortTextAsNumbers, DataOption3:=xlSortNormal
End Sub

Like this, much quicker, no autofilling needed if you already know how many rows you want to insert into, do it all in one step:

Sub CopyFormulasSort1()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

'Insert values via formulas
    Range("F3:F" & LR).FormulaR1C1 = _
        "=IF(AND(RC[-5]=R[-1]C[-5],RC[-2]<R[-1]C[-2]),RC[-2]+1,RC[-2])"
    Range("G3:G" & LR).FormulaR1C1 = _
        "=IF(AND(RC[-6]=R[-1]C[-6],RC[5]=R[-1]C[5],R[-1]C[-3]>RC[-3]),RC[-3]+1,IF(AND(RC[-6]=R[-1]C[-6],RC[5]=R[-1]C[5],R[-1]C<>R[-1]C[-3]),RC[-3]+1,RC[-3]))"
    Range("H3:H" & LR).FormulaR1C1 = "=IF(RC[-7]<>R[-1]C[-7],RC[-4]+RC[-3],RC[-1]+RC[-3])"

'Remove Formulas
    Range("F3:H" & LR).Value = Range("F3:H" & LR).Value
    Range("A2").Select
End Sub

The code in blue is another way to remove formulas, just re-insert the "values".

Notice, we didn't select anything to do this work? Much faster.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].



(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)