Desire to make VBA code more efficient

[DonEB]

I'm a beginner working with VBA. I first used the "record a new macro" function in Excel to create my first set of Macro's. I progressed to the point of looking for snippets of code on the internet to accomplish specific tasks that needed to be done. However, I am very aware that my coding is very inefficient and I have been trying to identify ways to make improvements but I have been having difficulties applying my findings to my specific coding.

I am submitting some actual code here with the hopes that someone would be able to make it more efficient for me. In doing so, I would try to read up and understand WHY it is more efficient and then try to carry that logic forward to other areas within my program. If anyone is willing to help out with this endeavor, it would be greatly appreciated. Here is a snippet of my code.

This will represent only the initialization steps in my program and leads to more coding which is where I'm probably losing all my time but I think it is important to start off with correct/efficient coding. Thanks for any help and guidance anyone can provide. ​

'  Initialize all players that have been identified for tennis matches.
'  First select and copy players (values) that have been identified available
'     from worksheet TeamSelection AG45:AG60 and paste them to worksheet
'     TeamSelectin A8 (transposed)
 
    Sheets("TeamSelection").Select
    Range("AG45:AG60").Select
    Selection.Copy
 
    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    ' From Chart 10:
    ' Take original sorted sixteen players and place them (transposed) into AK24:AZ24
    '      This will initiate the process of identifying which 4 players have been selected
    '      to play and which players remain available to play on either TEAM # 2 or beyond
    '
   
    Range("AK24").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
  
'  Sort CHART 15 (Worksheet TeamSelection AG45:AY60) on column "CB" (Low-High) in order to
'  prepare for future LOOKUPS which requires player numbers to be in ALPHA or NUMERIC order
 
    Range("AG45:AY60").Select
    ActiveWorkbook.Worksheets("TEAMSELECTION").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TEAMSELECTION").Sort.SortFields.Add Key:=Range( _
        "AG45:AG60"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TEAMSELECTION").Sort
        .SetRange Range("AG45:AY60")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    '
    ' DETERMINE TEAM # 1
    ' Takes 1820 combination (i.e., =COMBIN(16,4)) of potential teams and copies values to
    ' columns G - J
    '
    Range("C7:F1826").Select
    Selection.Copy
    Range("G7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    ' Sort G7:S1826 by Column's K (Ascending), S (Descending) and R (Ascending)
    ' Purpose is to place all potential team options on top and all invalid team options at bottom
    '
    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

 

[RoryA]

It is very rarely necessary to ever select anything in VBA. So, for example, this part:

    Sheets("TeamSelection").Select
    Range("AG45:AG60").Select
    Selection.Copy
 
    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

can be reduced to:

    Sheets("TeamSelection").Range("AG45:AG60").Copy
     Sheets("TeamSelection").Range("A8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False

You can also use a With ... End with block to avoid repeating the worksheet:

  With Sheets("TeamSelection")
    .Range("AG45:AG60").Copy
    .Range("A8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=False
End With

 

[DonEB]

Thank you... That was helpful.

Would the same logic apply to the code I provided above that involved the SORT? Or is by using the macro recorder already the most efficient way to code it.

Thanks again.

 

[RoryA]

Yes, the same logic would apply there. For a simple sort like that, I would probably use the old Range.Sort method rather than the new Sort object.

 

[DonEB]

Thanks again... but I'm not familiar with the Range.Sort method but based on a google search I have come up with a solution for a single sort but most of my sorts are multiple in nature. Can you tell me if the following might be how a multiple sort would be written?

Here is the OLD version:

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​

Here is my proposed NEW version based on your suggestion:

I assume that when the sort is set (i.e.,

Range("K7")​

that it refers to the entire column within the sort range. And, I'm not 100% sure if the syntax is correct for the rest of the coding.

Dim SortRange As Range
Dim KeyCell1 As Range

Dim KeyCell2 As Range​

SetsortRange = Range("G7:S1826")
SetkeyCell1 = Range("K7")

SetkeyCell2 = Range("R7")​

<strike></strike>

sortRange.Sort Key1:=keyCell1, Order1:=xlAscending,

Key2:=keyCell2, Order2:=xlAscending,​

Header:=xlYes

Thanks for you input on this. It is appreciated. ​