performance - 提高 VBA 中的循环效率

Question

我有一个 For循环遍历整数 1 到 9 并简单地找到与该整数对应的最底部的条目(即 1,1,1,2,3,4,5 将找到第三个“1”条目)并插入一个空白行。我将数字与仅对应于此代码的应用程序的字符串“FN”连接起来，只是为了澄清。无论如何，它工作得很好，但它只需要运行 9 个整数就滞后了很多。我希望有人能够帮助我调试以提高此代码的速度。谢谢!
如果有人可以阐明一种用跨越的页面标题的格式化副本(“A1:L1”)填充正在插入的空白行的好方法，则可以加分。我尝试的代码在 Next i 之前被注释掉了.

Sub test()

Dim i As Integer, Line As String, Cards As Range
Dim Head As Range, LR2 As Long


        For i = 1 To 9
    Line = "FN" & CStr(i)
    Set Cards = Sheets(1).Cells.Find(Line, after:=Cells(1, 1), searchdirection:=xlPrevious)

    Cards.Rows.Offset(1).EntireRow.Insert
    Cards.Offset(1).EntireRow.Select
'    Range("A" & (ActiveCell.Row), "K" & (ActiveCell.Row)) = Range("A3:K3")
'    Range("A" & (ActiveCell.Row), "K" & (ActiveCell.Row)).Font.Background = Range("A3:K3").Font.Background

     Next i


End Sub

Answer 1

这对我来说工作得很快

Sub Sample()
    Dim i As Long, line As String, Cards As Range

    With Sheets(1)
        For i = 1 To 9
            line = "FN" & i

            Set Cards = .Columns(6).Find(line, LookIn:=xlValues, lookat:=xlWhole)

            If Not Cards Is Nothing Then
                .Range("A3:K3").Copy
                Cards.Offset(1, -5).Insert Shift:=xlDown
            End If
         Next i
    End With
End Sub

之前



之后