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

 

[johnnyL]

Try the following for your sort:

Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort key1:=Range("D2"), order1:=xlAscending, DataOption1:=xlSortTextAsNumbers, _
      key2:=Range("B2"), order2:=xlAscending, DataOption2:=xlSortNormal, Header:=xlYes

 

[keef2]

Try the following for your sort:

Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row).Sort key1:=Range("D2"), order1:=xlAscending, DataOption1:=xlSortTextAsNumbers, _
      key2:=Range("B2"), order2:=xlAscending, DataOption2:=xlSortNormal, Header:=xlYes

Thanks, I tried it but still get goofy results it didnt sort the box # correctly it seems:

Like i said this code seems to work just not really understanding why i have to add the 2nd range section for it to sort properly????:

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

 

[johnnyL]

The picture in your last post seems to be sorted fine.

 

[keef2]

The picture in your last post seems to be sorted fine.

Yes it is by Job # but then it should sort the box # in ascending order as well. So while the job # sorts I'm not sure why its not sorting Box #'s together.

Something like this:

 

[johnnyL]

It would be easier to assist you if you uploaded your results with XL2BB.

Which column do you definitely want completely sorted and which column do you want to be the secondary column which may or may not end up completely sorted?

 

[MARK858]

@keef2 Judging by your image in post 5 Key1 should be B2 and Key2 should be D2

 

[johnnyL]

@keef2 Judging by your image in post 5 Key1 should be B2 and Key2 should be D2

That is why I asked in post #6.

 

[MARK858]

That is why I asked in post #6.

I know, I just didn't see the need for an XL2BB screenshot in this instance based on what the OP posted

 

[johnnyL]

I planned on posting the different results from different sorts, but I don't feel like typing in all the data.