How can I implement variables and optimize the below code?

Question

I have a work project which calculates data in 1 WS and pastes the summary on another. Since in the same workbook I also have a variety of other sheets with charts, pivot tables or other code the calculation sometimes get slow. Since I'm a beginner most of my code is recorded macro, which makes it's a bit long. The code works, however, I'm wondering is there a way to make the code shorter? Some days ago I asked for a part of the calculations, however, I didn't manage to implement the provided solution. I would appreciate advice and ideas. Thank you

The below code takes the data from columns "L:M" clear them from the text, and moves them in front for calculations, the result of which is pasted on the "the statistics" worksheet. Column H has "IF" logical statement.

    Sub RemoveEUR()
 'remove  currency from cell

    Range("L3:M5000").Select

         Selection.Cut

    Range("F3:G5000").Select

         ActiveSheet.Paste


    Range("A3").Select

    Cells.Replace What:="eur", 
          Replacement:="",LookAt:=xlPart,SearchOrder:=xlByRows, _
          MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False

    Columns("I:N").Select

             Selection.Delete Shift:=xlToLeft

    Columns("E:G").Select

            Selection.NumberFormat = "€##,##0.00_)"

    Range("H3").Select

           ActiveCell.FormulaR1C1 = "=IF(RC[-1]>RC[-2],1,0)"

         Selection.AutoFill Destination:=Range("H3:H5000")

    Columns("H:H").Select

            Selection.HorizontalAlignment = xlCenter

            Columns(8).NumberFormat = "0"

    Worksheets("Statistics").Activate

    Range("F1").Select

       ActiveCell.FormulaR1C1 = _

        "=""Win Count = ""&SUM(GameList!R[2]C[2]:R[4999]C[2])&"" Out Of ""&COUNTA(GameList!C[-2])-1&"" GR"""


    Range("A4").Offset(1, 1).Select

            ActiveCell.FormulaR1C1 = "=COUNTA(GameList!C[-1])-2"

    Range("B6").Select

            ActiveCell.FormulaR1C1 = "=SUM(GameList!C[4])"

            ActiveCell.Offset(1, 0).FormulaR1C1 = "=AVERAGE(GameList!C[4])"

            ActiveCell.Offset(2, 0).FormulaR1C1 = "=SUM(GameList!C[4])-SUM(GameList!C[5])"

            ActiveCell.Offset(3, 0).FormulaR1C1 = "=R[-1]C/R[-3]C"

            Selection.NumberFormat = "€#,##0.00;[Red]-€#,##0.00"
End Sub

[How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/62576) — Ken White, Jul 22 '19 at 01:15

Paula Yankova · Answer 1 · 2019-07-23T01:15:05.967

I want to thank all the contributors here who are giving the answers Like @Ken White & @peege, and my Udemy lecturer Alan Jarvis who with their suggestions helped to find my solution :)

Sub RemoveEUR() 'remove currenvy from cell & calculate stats

Range("L3:M5000").Select
     Selection.Cut
Range("F3:G5000").Select
     ActiveSheet.Paste

Range("A3").Select
Cells.Replace What:="eur", Replacement:=" ", _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("I:I,J:J,K:K,L:L,M:M,N:N").Delete
Columns("E:G").NumberFormat = "€##,##0.00_)"

Range("D:H").HorizontalAlignment = xlCenter
Range("H3:H10000").Formula = "=IF(RC[-1]>RC[-2],1,0)"
Columns(8).NumberFormat = "0"

Worksheets("Player's Statistics").Activate

Range("F1").Select
ActiveCell.FormulaR1C1 = _
    "=""Win Count = ""&SUM(GameList!R[2]C[2]:R[4999]C[2])&"" Out Of ""&COUNTA(GameList!C[-2])-2&"" GR"""

Range("A4").Offset(1, 1).Select
        ActiveCell.FormulaR1C1 = "=COUNTA(GameList!C[-1])-1"
Range("B6").Select
        ActiveCell.Formula = "=SUM(GameList!C[4])"
        ActiveCell.Offset(1, 0).Formula = "=AVERAGE(GameList!C[4])"
        ActiveCell.Offset(2, 0).Formula = "=SUM(GameList!C[4])-SUM(GameList!C[5])"
        ActiveCell.Offset(3, 0).Formula = "=R[-1]C/R[-3]C"
        Selection.NumberFormat = "€#,##0.00;[Red]-€#,##0.00"

End Sub

How can I implement variables and optimize the below code?

1 Answers1