vba - 如何提高以下代码的效率

Question

关闭。这个问题需要多问 focused 。目前不接受答案。

---

想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post .

已关闭 8 年前。

Improve this question

谁能帮我优化一下下面的简单代码需要很长时间才能完成执行。也许我在某个地方遇到了无限循环。它所做的只是获取两个字符串(如果它们相等)，然后根据此处提到的单元格重新排列位置。

 Sub sort()

 Dim astid As String
 Dim partno As String
 Dim FinalRow As Long
 Dim i, j As Integer

 FinalRow = Sheets("Combined Version").Range("H9000").End(xlUp).Row

 For i = 5 To FinalRow

    partno = Sheets("Combined Version").Cells(i, 7).Value

    For j = 5 To FinalRow

       astid = Sheets("Combined Version").Cells(j, 8).Value

        If astid = partno Then

            Cells(j, 8).Select
            Selection.Copy
            Range("N5").Select
            ActiveSheet.Paste

            Cells(i, 8).Select
            Application.CutCopyMode = False
            Selection.Copy
            Cells(j, 8).Select
            ActiveSheet.Paste

            Range("N5").Select
            Application.CutCopyMode = False
            Selection.Copy
            Cells(i, 8).Select
            ActiveSheet.Paste

        End If

    Next j
Next i
End Sub

Answer 1

没有必要使用iterim N5作为临时保存区域，因为您已经将值存储在astid变量中。

Sub mysort()
    Dim astid As String, partno As String
    Dim fr As Long, i, j As Long

    With Sheets("Combined Version")
        fr = .Cells(Rows.Count, "H").End(xlUp).Row
        For i = 5 To fr
            partno = .Cells(i, 7).Value2
            For j = 5 To fr
                astid = .Cells(j, 8).Value2
                If LCase(astid) = LCase(partno) Then
                    .Cells(j, 8) = .Cells(i, 8).Value2
                    .Cells(i, 8) = astid
                End If
            Next j
        Next i
    End With
End Sub

With ... End With statement的使用减少了识别工作表的重复调用。

使用变体数组可以加快速度。