Looking for VBA code to efficiently Sort of Multiple Columns

[DonEB]

I originally used the following to code to successfully perform my sort.

Range("g7:S1826").Select
ActiveWorkbook.Worksheets("TeamSelection").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("TeamSelection").Sort.SortFields.Add Key:=Range( _
"k7:k1826"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("TeamSelection").Sort.SortFields.Add Key:=Range( _
"R7:R1826"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("TeamSelection").Sort
.SetRange Range("g7:S1826")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

However, I am looking to make my code more efficient and so I am trying to use VBA coding.​

But, when I attempted to perform a sort using the following VBA code I got a "Run-time error '1004': To do this, all the merged cells need to be the same size". I don't understand why.

With Sheets("TeamSelection")

.
.
.
​

Set sortRange = Range("G7:S1826")​

Set keyCell = Range("K7")​

Set keyCell2 = Range("R7")
​

sortRange.Sort Key1:keyCell, Order1:=xlAscending, Key2:=keyCell2, Order2:=xlAscending, Header:=xlYes
.
.
.
​

End With


Any assistance would be appreciated.

Thank you,
Don
​

 

[DonEB]

I have a follow-up question if you don't mind... this is related directly to the code discussed above.

In one section of my code I was able to use the code above and (after your suggestions) it worked perfectly. Now... I'm trying to replace code in another section of my program with similar code as above and have come up with another error. The error is...

"sort method of range class failed error 1004" ​

I'm trying to replace the following code ....

Range("g7:S501").Select
    ActiveWorkbook.Worksheets("TeamSelection2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TeamSelection2").Sort.SortFields.Add Key:=Range( _
        "k7:k501"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("TeamSelection2").Sort.SortFields.Add Key:=Range( _
        "R7:R501"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TeamSelection2").Sort
        .SetRange Range("g7:S501")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

with this code ...

With Sheets("TeamSelection2")
        .Range("C7:F501").Copy
        .Range("G7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With

Any thoughts? It seems like it should be a very clean replacement but I'm obviously missing something.

Thanks,
Don

 

[Fluff]

I not sure I understand.
The code you are trying to use doesn't do any sorting, therefore you should not get that error.

Also when posting code, lease use code tags (The # icon in the reply window).

 

[DonEB]

My error sorry... I pasted the wrong code. The code I meant to include and the code I'm trying to use is...

With Sheets("TeamSelection2")
​

Set sortRange = Range("G7:S501")
Set keyCell = Range("K7")
Set keyCell2 = Range("R7")
sortRange.Sort Key1:=keyCell, Order1:=xlAscending, Key2:=keyCell2, Order2:=xlAscending, Header:=xlYes

End With​

 

[Fluff]

You haven't properly qualified the ranges.
Try

With Sheets("TeamSelection2")
   Set SortRange = .Range("G7:S501")
   Set Keycell = .Range("K7")
   Set Keycell2 = .Range("R7")
   SortRange.Sort Key1:=Keycell, Order1:=xlAscending, Key2:=Keycell2, Order2:=xlAscending, header:=xlYes
End With

 

[DonEB]

That was it! You've been a big help. Thanks again!

Don

 

[Fluff]

You're welcome