Sort query

CookieMonster76

Board Regular
Joined
Apr 30, 2015
Messages
175
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,930
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 30, 2015
Messages
175
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,930
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

Board Regular
Joined
Apr 30, 2015
Messages
175
Ok, how about
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,886
Messages
5,574,833
Members
412,620
Latest member
sharma7s
Top