更改了一些东西。如前所述，Copy和Activate是性能上最大的拖累。我引入了With语句，而不是Activate，并将Copy、Paste更改为更快的....Value = ....Value

Sub dup_cp()

Dim i As Integer
Dim j As Integer
Dim k As Integer

With Sheets("Investment Assets")
    j = Application.CountA(.Range("A:A"))
    'counts the number of filled in rows

    For i = 5 To j
    'it starts from line 5 on purpose, the ISIN numbers start from that line
        For k = i + 1 To j
            If .Cells(k, 55).Value = "Duplicate Value" Then GoTo skip_dup
            'it skips the line that has already been detected as duplicated

            If .Cells(k, 1).Value = .Cells(i, 1).Value Then
            'it finds the duplicate value (ISIN number) in the first column
                If .Cells(k, 29).Value >= .Cells(i, 29).Value Then
                'it compares the 29th column values (the modified duration of the components) and keeps the bigger value for prudency reasons
                    .Range(.Cells(i, 15), .Cells(i, 32)).Value = .Range(.Cells(k, 15), .Cells(k, 32)).Value
                Else
                    .Range(.Cells(k, 15), .Cells(k, 32)).Value = .Range(.Cells(i, 15), .Cells(i, 32)).Value
                End If
                .Cells(k, 55).Value = "Duplicate Value"
                'it shows in the 55th column if the ISIN number is duplicated or not
                .Cells(i, 55).Value = "Duplicate Value"
            Else
                .Cells(k, 55).Value = "-"
            End If
skip_dup:
        Next
    Next
End With

End Sub

老Nick的提议对于性能来说也是非常好的，但是我会小心地实现它，就像这样：

Sub xxx

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    'Your code

ErrorHandler:
    If Err.Number <> 0 Then MsgBox Err.Number & " " & Err.Description
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

因为如果你一开始就禁用了这些东西，然后突然代码中出现了问题，你可能就无法重新启用这些东西了。

在不改变你所做的任何事情的情况下（毕竟你说它工作正常），你可以在调用你的子程序之前尝试禁用Excel的一些自动功能：

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

然后在你从潜艇回来的时候重新启用它们：

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

希望通过这样做您可以看到执行速度的提高