Help Understanding my Sort VBA code

[keef2]

Hi all,

The issue was I had a multilevel sort code that I wanted to sort by Job # then By Box # both in ascending order. However, for some reason I noticed in multiple areas that it was not properly sorted mostly because the data has a bunch of different style job #'s. Here is what was happening originally: (take a look at box 221 where the 1701 Cicero project was in the middle of 3XXX projects for no reason.

Here was my original code:

Sub MultiLevelSort()

Worksheets("User Inputs").Sort.SortFields.Clear

Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("D2"), Key2:=Range("B2"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlAscending, DataOption1:=xlSortTextAsNumbers
 
End Sub

My new code which applies the correct sorting is the following. Can anyone help explain why I needed to add the 2nd range sort to get this to work properly? Or is there a better way to code this? Thanks in advance!

Sub MultiLevelSort()

Worksheets("User Inputs").Sort.SortFields.Clear

Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("D2"), Key2:=Range("B2"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlAscending, DataOption1:=xlSortTextAsNumbers
   
Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("B2"), Header:=xlYes, _
    Order1:=xlAscending
   
End Sub

 

[keef2]

It XL2BB didn't work on that sheet for some reason.... Would just lock up excel every time I tried even tho it works great on other workbooks.

To be clear i want it to sort by job # in ascending order. Then by Box number in ascending order. Like i said my code works i just don't understand why i needed the 2nd range to get it to work how i needed it from the above screen shot.

That said only thing i can think of is i used the LEN function in an equation so I sorted the range by DataOption1:=xlSortTextAsNumbers then with X1sortnormal.

 

[keef2]

Hope this is clearer, ultimately I want it sorted by box #. However before that i want all job #'s for those box #'s to be sorted and not the 33XX between the 22xxxxxxx numbers. Basically all the 33's and all the 22s should be grouped in ascending order within each box #.

 

[MARK858]

Did you swap the keys around as per post number 7?

 

[MARK858]

Deleted as looked at the wrong table, question in my last post still stands

 

[johnnyL]

It XL2BB didn't work on that sheet for some reason.... Would just lock up excel every time I tried even tho it works great on other workbooks.

You don't need to to try and capture the entire sheet with XL2BB. You could just do like 50 or 100 rows.

XL2BB has a limit of like 3000 cells if I recall correctly.

 

[keef2]

Did you swap the keys around as per post number 7?

yes that sorts by box # then job #. Basically creates a job # ascending list that doesn't group all box's together. Again tried to switch D2 and B2: Same result (see screen shot).

Again this is the code that does what i want it to here (just trying to understand if there is better approach or understand why i need to have redudant code to get it to sort how i wanted by job# and grouped with each box # together.:

Sub MultiLevelSort()

Worksheets("User Inputs").Sort.SortFields.Clear

Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("D2"), Key2:=Range("B2"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlAscending, DataOption1:=xlSortTextAsNumbers
   
Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("B2"), Header:=xlYes, _
    Order1:=xlAscending
   
End Sub

Here was the code i tried to see if that would fix the sort without having to do it again.

Sub MultiLevelSort()

Worksheets("User Inputs").Sort.SortFields.Clear

Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("B2"), Key2:=Range("D2"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlAscending, DataOption1:=xlSortTextAsNumbers

End Sub

 

[keef2]

You don't need to to try and capture the entire sheet with XL2BB. You could just do like 50 or 100 rows.

XL2BB has a limit of like 3000 cells if I recall correctly.

I know still doesnt work...

 

[johnnyL]

If you say so.

Perhaps you could provide a link to the workbook with any 'Sensitive' info changed?

 

[keef2]

So i think this is solved. Sorry that I couldn't get XLB22 to work on this workbook for some reason even tried to reinstall etc.

Long story short. I used LEN function in the equation to get the job #, thus it was showing some data as text while mixed with values. So i added the Value function to the equation:

New formula in column D = =IF(A13471="","",VALUE(RIGHT(A13471,LEN(A13471)-FIND("#",A13471))))
Previous formula in column D = =IF(A13247="","",RIGHT(A13247,LEN(A13247)-FIND("#",A13247)))

New VBA code i used:

Sub MultiLevelSort()
    
    Dim sht As Worksheet: Set sht = ThisWorkbook.Sheets("User Inputs")
    bot_row = sht.UsedRange.Rows.Count
    sht.Sort.SortFields.Clear

    sht.Range("A2:G" & bot_row).Sort Key1:=sht.Range("B2"), Order1:=xlAscending, Key2:=sht.Range("D2"),  Order2:=xlAscending, Header:=xlYes

End Sub

 

[MARK858]

Happy that you got it sorted