Francis Hookham said:
I should like to sort the following range (Orientation:=xlTopToBottom)
Range(Cells(4, 19), Cells(lastRow, 22))
first by C21 and then, after transferring data from that sorting, sort the
same range by C22 in order to transfer the data in a different order
There is no header row
Of course I could record the selecting, sorting, copying and pasting as I
have done in the past but could do it far more smoothly if you could get me
over the sorting hurdle
First - I'll make the minor assumption that sorting "first by C21"
means "first by Column 21", since cell C21 is not part of the range.
Second, my preferred method of writing this would be to record the
macro, for which I got
Selection.Sort Key1:=Range("U4"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
and modify it, substituting your range for Selection, and setting
the key = the first cell in column 21:
Cells(4, 19).Resize(lastRow - 3, 4).Sort _
Key1:= Cells(4, 21), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
(There's no particular reason to put each argument on a separate
line - I just find it clearer.)
In general I recommend getting away from using Selection in your
macros - it's slower than using the range objects directly, it makes
your code longer, and it's generally harder to maintain since you
have to figure out what's selected in order to know what range a
particular command is operating on. You almost never need to use it.
For instance, instead of something like
Range(Cells(4, 19), Cells(lastRow, 22)).Select
Selection.Sort Key1:=Cells(4, 21), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range(Cells(4, 19), Cells(lastRow, 22)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Cells(4, 22), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Selection.Copy
Sheets("Sheet2").Select
Range("J1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
I'd use (note the change in Key1 to be the cell in row 1, column 3
(or 4) of the range - that way only one line has to be changed if
you add/delete rows or columns):
With Cells(4, 19).Resize(lastRow - 3, 4)
.Sort _
Key1:= .Cells(1, 3), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Copy Destination:=Sheets("Sheet2").Range("A1")
.Sort _
Key1:= .Cells(1, 4), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.Copy Destination:=Sheets("Sheet2").Range("J1")
End With
