excel - 如何使这个 VBA 代码 - Excel 更有效率？

Question

最好的祝福; 我有以下代码，我想让它更有效率，因为要分析 1,000,000 条记录。非常感谢，为我的英语悲伤道歉。

Sub test()
Sub test()
    Dim value1 As Worksheet, value2 As Worksheet
    Dim col1 As Long, col2 As Long
    Set value1 = Worksheets(2)
    Set value2 = Worksheets(2)
    For col1 = 2 To value1.Range("A2").End(xlDown).Row
        For col2 = 2 To value2.Range("B2").End(xlDown).Row
            If value1.Cells(col1, 1).Value = value2.Cells(col2, 2).Value _
                And value1.Cells(col1, 1).Value > 0 Then
                    value1.Cells(col1, 1).Interior.Color = vbYellow
            End If
        Next
    Next
End Sub

标签： excelvbaexcel-formula

Answer 1

我无聊了，为你做了一件事。

Private Sub utqwdelkdfjsvd()
    Dim rng As String
    Dim i As Long, j As Long
    Dim pickup As Variant
    pickup = ThisWorkbook.Worksheets("yoursheetname").usedrange
    For i = LBound(pickup, 1) To UBound(pickup, 1)
        For j = LBound(pickup, 1) To UBound(pickup, 1)
            If pickup(i, 1) = pickup(j, 2) And pickup(i, 1) > 0 Then
                If i = 1 Then
                    rng = "a" & i
                Else
                    rng = rng & ", a" & i
                End If
            End If
        Next j
    Next i
    ThisWorkbook.Worksheets("Sheet1").Range(rng).Interior.ColorIndex = 4
End Sub