Sort query

CookieMonster76

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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
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
180
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
55,794
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
180
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,128,125
Messages
5,628,856
Members
416,344
Latest member
Maug2004

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
Top