Application Custom List

P

pwill

Active Member
Joined
Nov 22, 2015
Messages
406
Hi, can anyone help with Application Custom List to Sort Data

I have a command button on sht2 with the following code to sort data on sht4 but excel will not save and crashes after running the macro. I have tried tinkering around a little with the code but keep getting errors.

For example I have tried changing the +2 to +6 in the code as there is 5 Default Custom Lists already exiting within excel and this allowed me to save after running the code but did not sort on sht4 the way it should?

Any help would be appreciated

Code: 
Private Sub CommandButton110_Click()
    Dim Sht2 As Worksheet: Set Sht2 = Sheet02
    Dim Sht4 As Worksheet: Set Sht4 = Sheet04
    Dim ListNum As Long
    Dim lRowA As Long
    Dim lRowM As Long
    
        Application.ScreenUpdating = False
            
        lRowA = Sht4.Cells(Rows.Count, "A").End(xlUp).Row
        lRowM = Sht4.Cells(Rows.Count, "M").End(xlUp).Row
        
        Sht4.Range("A2:A" & lRowA) = _
            Sht4.Range("E2:E" & lRowA).Value
        Sht4.Range("M2:M" & lRowM) = _
            Sht4.Range("Q2:Q" & lRowM).Value
                
        ListNum = Application.CustomListCount + 2
        Application.AddCustomList Array("0", "1", "-1", "2", "-2", "3", "-3", "4", "-4", "5", "-5", "6", "-6", "7", "-7", "8", "-8", "9", "-9", "10", "-10", "11", "-11", "12", "-12", "13", "-13", "14", "-14", "15", "-15", "16", "-16", "17", "-17", "18", "-18", "19", "-19", "20", "-20", "21", "-21", "22", "-22", "23", "-23", "24", "-24", "25", "-25", "26", "-26", "27", "-27", "28", "-28", "29", "-29", "30", "-30", "31", "-31", "32", "-32", "33", "-33", "34", "-34", "35", "-35", "36", "-36", "37", "-37", "38", "-38", "39", "-39", "40", "-40", "41", "-41", "42", "-42", "43", "-43", "44", "-44", "45", "-45", "46", "-46", "47", "-47", "48", "-48", "49", "-49", "50", "-50", "51", "-51", "52", "-52", "53", "-53", "54", "-54", "55", "-55", "56", "-56", "57", "-57", "58", "-58", "59", "-59", "60")
            With Sht4
                .Range("A2:K" & lRowA).Sort Key1:=.Range("E2"), order1:=xlAscending, _
                    Header:=xlNo, OrderCustom:=ListNum
                .Range("M2:W" & lRowM).Sort Key1:=.Range("Q2"), order1:=xlAscending, _
                    Header:=xlNo, OrderCustom:=ListNum
            End With
        ListNum = ListNum - 1
        Application.DeleteCustomList ListNum
        
    Application.ScreenUpdating = True
    
End Sub

Regards


pwill
​
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Try adding this line
Code: 
                 [COLOR=#ff0000] .Range("A1").Sort key1:=.Range("A1"), ordercustom:=1[/COLOR]
            End With
 
Upvote 0
Thanks Fluff,

That almost worked, it did save this time but the sort came out wrong with "A:K" and sorted correct with "M:W"

ie the sort is in reverse with "A:K" and the headers have gone to the bottom​

[TABLE="width: 711"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]43258[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]REF[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Cnt1[/TD]
[TD="align: center"]Cnt2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43257[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43257[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]-21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43258[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43258[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]-11[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43259[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43259[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-18[/TD]
[TD="align: center"]-20[/TD]
[TD="align: center"]-30[/TD]
[TD="align: center"]-31[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43259[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43259[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-12[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43260[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43259[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]-21[/TD]
[TD="align: center"]-22[/TD]
[TD="align: center"]-27[/TD]
[TD="align: center"]-28[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43260[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43260[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]-12[/TD]
[TD="align: center"]-25[/TD]
[TD="align: center"]-27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43261[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43260[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43261[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43261[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43262[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43261[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-8[/TD]
[TD="align: center"]-11[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43262[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43261[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-15[/TD]
[TD="align: center"]-21[/TD]
[TD="align: center"]-23[/TD]
[TD="align: center"]-26[/TD]
[TD="align: center"]-28[/TD]
[TD="align: center"]-34[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43263[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"]-7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43261[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]REF[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Cnt1[/TD]
[TD="align: center"]Cnt2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43262[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-16[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]-21[/TD]
[TD="align: center"]-26[/TD]
[TD="align: center"]-30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43262[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]-17[/TD]
[TD="align: center"]-33[/TD]
[TD="align: center"]-38[/TD]
[TD="align: center"]-43[/TD]
[TD="align: center"]-47[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43262[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-11[/TD]
[TD="align: center"]-30[/TD]
[TD="align: center"]-31[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43262[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]-12[/TD]
[TD="align: center"]-31[/TD]
[TD="align: center"]-32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]43263[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]-23[/TD]
[TD="align: center"]-32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43257[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-13[/TD]
[TD="align: center"]-18[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43257[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43258[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43259[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43260[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-7[/TD]
[TD="align: center"]-8[/TD]
[TD="align: center"]-9[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43261[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43262[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"]-29[/TD]
[TD="align: center"]-30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]43263[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]43257[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]-18[/TD]
[TD="align: center"]-23[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
And I have the code as

Code: 
Private Sub CommandButton110_Click()
    Dim Sht2 As Worksheet: Set Sht2 = Sheet02
    Dim Sht4 As Worksheet: Set Sht4 = Sheet04
    Dim ListNum As Long
    Dim lRowA As Long
    Dim lRowM As Long
    
        Application.ScreenUpdating = False
            
        lRowA = Sht4.Cells(Rows.Count, "A").End(xlUp).Row
        lRowM = Sht4.Cells(Rows.Count, "M").End(xlUp).Row
        
        Sht4.Range("A2:A" & lRowA) = _
            Sht4.Range("E2:E" & lRowA).Value
        Sht4.Range("M2:M" & lRowM) = _
            Sht4.Range("Q2:Q" & lRowM).Value
                
        ListNum = Application.CustomListCount + 2
        Application.AddCustomList Array("0", "1", "-1", "2", "-2", "3", "-3", "4", "-4", "5", "-5", "6", "-6", "7", "-7", "8", "-8", "9", "-9", "10", "-10", "11", "-11", "12", "-12", "13", "-13", "14", "-14", "15", "-15", "16", "-16", "17", "-17", "18", "-18", "19", "-19", "20", "-20", "21", "-21", "22", "-22", "23", "-23", "24", "-24", "25", "-25", "26", "-26", "27", "-27", "28", "-28", "29", "-29", "30", "-30", "31", "-31", "32", "-32", "33", "-33", "34", "-34", "35", "-35", "36", "-36", "37", "-37", "38", "-38", "39", "-39", "40", "-40", "41", "-41", "42", "-42", "43", "-43", "44", "-44", "45", "-45", "46", "-46", "47", "-47", "48", "-48", "49", "-49", "50", "-50", "51", "-51", "52", "-52", "53", "-53", "54", "-54", "55", "-55", "56", "-56", "57", "-57", "58", "-58", "59", "-59", "60")
            With Sht4
                .Range("A2:K" & lRowA).Sort Key1:=.Range("E2"), order1:=xlAscending, _
                    Header:=xlNo, OrderCustom:=ListNum
                .Range("M2:W" & lRowM).Sort Key1:=.Range("Q2"), order1:=xlAscending, _
                    Header:=xlNo, OrderCustom:=ListNum
                [COLOR=#ff0000].Range("A1").Sort key1:=.Range("A1"), ordercustom:=1[/COLOR]
            End With
        ListNum = ListNum - 1
        Application.DeleteCustomList ListNum
        
    Application.ScreenUpdating = True
    
End Sub
 
Last edited:
Upvote 0
Fluff said:
Try adding this line
Code: 
                 [COLOR=#ff0000] .Range("A1").Sort key1:=.Range("A1"), ordercustom:=1[/COLOR]
            End With
Click to expand...


Thanks Fluff,

I have managed to work it out, everything sorts as it should now and also allows me to save after running the code :)
This line did the trick
Code: 
[COLOR=#ff0000][FONT=Verdana][LEFT]                             .Range("A1", "M1").Sort key1:=.Range("A1", "M1"), ordercustom:=1[LEFT][COLOR=#574123][FONT=monospace][I]            End With[/I][/FONT][/COLOR][/LEFT]
[/LEFT]
[/FONT][/COLOR]



thanks again

pwill
​
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Is there a way for this VBA code to Ignore the spaces & give the results at the top of the page?
2
Replies
10
Views
1K
Alex Blakenburg
A
E
  • Solved
Custom Sorting VBA
Replies
3
Views
420
eli_m
E
T
  • Question Question
trying to understand why my code is crashing
Replies
4
Views
448
Peter_SSs
Peter_SSs
sparky2205
  • Question Question
COUNTIF with DATES
Replies
3
Views
123
sparky2205
sparky2205
An Quala
  • Solved
Change the code from Exact Match to Phrase Match
Replies
4
Views
515
An Quala
An Quala

Forum statistics

Threads
1,226,240
Messages
6,189,823
Members
453,573
Latest member
adefonzo23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top