Sort query

[CookieMonster76]

Hi

Searching around, i have found this as an efficient way to sort some data:

Worksheets("Sheet5").Sort.SortFields.Clear
Range("A4:M" & Cells(Rows.Count, "A").End(xlUp).Row).Sort Key1:=Range("L4:L" & Cells(Rows.Count, "A").End(xlUp).Row), Order1:=xlAscending, Header:=xlYes

I have then performed another action on it, and then need to re-sort it, but this time based on the criteria of 2 columns.

The macro recorder (amended for the range) give me this:

ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add2 Key:=Range("B4:B" & Cells(Rows.Count, "A").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add2 Key:=Range("D4:D" & Cells(Rows.Count, "A").End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet5").Sort
.SetRange Range("A4:K" & Cells(Rows.Count, "A").End(xlUp).Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Is there a way to shrink it down in a similar fashion to the single column sort?

Thanks

Paul

 

[Fluff]

Do you want to sort cols A:K or A:M & do you want to sort on L,B & D or just B & D?

 

[CookieMonster76]

Do you want to sort cols A:K or A:M & do you want to sort on L,B & D or just B & D?

The first part - There is data in columns A to M, and I want to sort on Column L.

Once that sort is done I do something else (as an aside, this part is deleting rows I don't need. If I don't do the above sort it takes ages to delete non sequential rows. Maybe there is a way to do this better?).

The second sort - There is now data only in cells A to K, and i want to sort by Col B, then by Col D.

 

[Fluff]

Ok, how about

Sub CookieMonster()
   With Sheets("sheet5")
      With .Range("A4:K" & .Range("A" & Rows.Count).End(xlUp).Row)
         .Sort .Range("B4"), xlAscending, .Range("D4"), , xlAscending, , , xlYes
      End With
   End With
End Sub

 

[CookieMonster76]

Ok, how about

Sub CookieMonster()
   With Sheets("sheet5")
      With .Range("A4:K" & .Range("A" & Rows.Count).End(xlUp).Row)
         .Sort .Range("B4"), xlAscending, .Range("D4"), , xlAscending, , , xlYes
      End With
   End With
End Sub

Thanks, perfect

For my learning/understanding. could i ask what the logic of what it's doing is? I get the use Sheet5, and the data in row A4:K, for the length of the data in col A, but what is the next line doing?

Thanks again

Paul

 

[Fluff]

It's doing the sort in the same way that your 1st code, I've just left out the argument names because I'm lazy.

Range.Sort method (Excel)

Office VBA reference topic

docs.microsoft.com