VBA代码速度慢，需要优化

Question

任何人都可以帮助优化我的代码而不使我的 Excel 崩溃吗？我把它放入 3 个不同的宏中，因为它卡住了我的 Excel。这主要是使用录音机完成的。

不确定罪魁祸首是否是vlookups、大数据集，或者只是因为我主要使用记录器，因此我没有任何捷径。

任何人都可以帮助合并此代码并使其运行更流畅吗？

 Sub finalversion1()

''original filter logic

    ActiveSheet.Range("$A$1:$DN$11800").AutoFilter Field:=109, Criteria1:= _
        "=Foreign Exchange Option", Operator:=xlOr, Criteria2:= _
        "=Standalone Cash Ticket Trade"
    Sheets("valumeasure").Select
    ActiveSheet.Range("$A$1:$AB$8134").AutoFilter Field:=9, Operator:= _
        xlFilterValues, Criteria2:=Array(0, "10/31/2040", 0, "12/3/2035", 0, "10/6/2034", 0 _
        , "6/24/2033", 0, "12/29/2032", 0, "6/23/2031", 0, "11/25/2030", 0, "10/9/2029", 0, _
        "11/1/2028", 0, "12/21/2027", 0, "8/31/2026", 0, "11/19/2025", 0, "11/29/2024", 0, _
        "11/14/2023", 0, "12/28/2022", 0, "11/17/2021", 0, "12/14/2020", 0, "12/30/2019", 0, _
        "12/31/2018", 2, "5/17/2017", 2, "5/18/2017", 2, "5/19/2017", 2, "5/22/2017", 2, _
        "5/23/2017", 2, "5/24/2017", 2, "5/25/2017", 2, "5/26/2017", 2, "5/30/2017", 2, _
        "5/31/2017", 1, "6/30/2017", 1, "7/31/2017", 1, "8/30/2017", 1, "9/29/2017", 1, _
        "10/31/2017", 1, "11/30/2017", 1, "12/29/2017")

        End Sub


 Sub finalversion2()

'' vlookup file

    Worksheets("valumeasure").Columns(3).Copy Destination:=Sheets("File").Columns(1)  ''copy and paste filtered values from valuation measure file
    Worksheets("eodcpos").Columns(2).Copy Destination:=Sheets("File").Columns(2)  ''copy and paste filtered values eodc

   ''Looking up into eodc position file
    Worksheets("File").Activate

    Range("C2").Select
    ActiveCell = "=VLOOKUP(A2,B:B,1,FALSE)"
    Selection.AutoFill Destination:=Range("C2:C8278")




    ''starting here we bring in eodc data
    Range("D2").Select
    ActiveCell = "=VLOOKUP(C2,eodcpos!B:BK,62,FALSE)"
    Selection.AutoFill Destination:=Range("D2:D8278")
    Range("E2").Select
    ActiveCell = "=VLOOKUP(C2,eodcpos!B:BK,17,FALSE)"
    Selection.AutoFill Destination:=Range("E2:E8278")
    Range("F2").Select
    ActiveCell = "=VLOOKUP(C2,eodcpos!B:BK,27,FALSE)"
    Selection.AutoFill Destination:=Range("F2:F8278")
    Range("G2").Select
    ActiveCell = "=VLOOKUP(C2,eodcpos!B:BK,57,FALSE)"
    Selection.AutoFill Destination:=Range("G2:G8278")
    Range("H2").Select
    ActiveCell = "=VLOOKUP(C2,eodcpos!B:DE,108,FALSE)"
    Selection.AutoFill Destination:=Range("H2:H8278")

    ''now looking up into valuation measure file.

        Range("I2").Select
    ActiveCell = "=VLOOKUP(C2,valumeasure!C:U,7,FALSE)"
    Selection.AutoFill Destination:=Range("I2:I8278")

        Range("J2").Select
    ActiveCell = "=VLOOKUP(C2,valumeasure!C:U,3,FALSE)"
    Selection.AutoFill Destination:=Range("J2:J8278")

     Range("K2").Select
    ActiveCell = "=VLOOKUP(C2,valumeasure!C:U,19,FALSE)"
    Selection.AutoFill Destination:=Range("K2:K8278")

        Range("L2").Select
    ActiveCell = "=VLOOKUP(C2,valumeasure!C:U,8,FALSE)"
    Selection.AutoFill Destination:=Range("L2:L8278")

    ''headers

              End Sub



      Sub finanlversion4()



Dim rng As Range

''sample file creation
Worksheets("Sample File").Activate




''values


    Dim src As Range
    Set src = Worksheets("File").Range("2:8278")

    Dim dst As Range
    Set dst = Worksheets("Sample File").Range("3:8279")

    ' sample file creation
    ' values

    dst.Columns("A") = "CSH"   ' hardcode
    dst.Columns("D") = "1"
    dst.Columns("G") = "USD"
    dst.Columns("J") = "DEALT"
    dst.Columns("N") = "0"

    dst.Columns("B") = src.Columns("D").Value
    dst.Columns("C") = src.Columns("E").Value
    dst.Columns("E") = src.Columns("F").Value
    dst.Columns("F") = src.Columns("F").Value
    dst.Columns("H") = src.Columns("L").Value
    dst.Columns("I") = src.Columns("G").Value
    dst.Columns("M") = src.Columns("I").Value
    dst.Columns("O") = src.Columns("K").Value

    dst.Columns("P") = "=IF(RC[-1]<0,""Y"",""N"")"



End Sub

Answer 1

仅举几例。

多次查找与一次查找

您正在多次查找 C2(使用 VLOOKUP)。您可以使用 MATCH() 函数将其替换为ONE 查找，并使用 INDEX 检索值。
注意:此外，如果可以的话，请考虑使用近似匹配。这只能在干净的排序数据上完成，但速度更快。

复制整列

考虑仅复制包含数据的范围，而不是整个列。

应用程序.计算/屏幕更新

如Mat's Mug correctly pointed out ，您还应该考虑在运行代码时关闭一些 Excel 应用程序设置，并在最后重新打开它们，以执行它们只执行一次的操作。
Application.ScreenUpdating Property
Application.Calculation Property