Improving Speed Comparing Multiple Columns

[princ_wns]

Plese try this one.

Sub OptimzeSpeed()

    Dim varArrSheet1        As Variant
    Dim varArrSheet2        As Variant
    
    Dim lngCtr1             As Long
    Dim lngCtr2             As Long
    
    Const strSheetName1      As String = "CURRENT"
    Const strSheetName2      As String = "PREVIOUS"
    
    varArrSheet1 = ThisWorkbook.Worksheets(strSheetName1).UsedRange
    varArrSheet2 = ThisWorkbook.Worksheets(strSheetName2).UsedRange
    
    ThisWorkbook.Worksheets(strSheetName1).UsedRange.ClearContents
    
    
    For lngCtr1 = LBound(varArrSheet1) To UBound(varArrSheet1)
        For lngCtr2 = LBound(varArrSheet2) To UBound(varArrSheet2)
            If varArrSheet1(lngCtr1, 5) = varArrSheet1(lngCtr2, 5) Then
                If varArrSheet1(lngCtr1, 10) <> varArrSheet1(lngCtr2, 10) Then
                    varArrSheet1(lngCtr1, 18) = varArrSheet1(lngCtr2, 10)
                End If
                
                If varArrSheet1(lngCtr1, 11) <> varArrSheet1(lngCtr2, 11) Then
                    varArrSheet1(lngCtr1, 19) = varArrSheet1(lngCtr2, 11)
                End If
                
                If varArrSheet1(lngCtr1, 12) <> varArrSheet1(lngCtr2, 12) Then
                    varArrSheet1(lngCtr1, 20) = varArrSheet1(lngCtr2, 12)
                End If
            End If
        Next
    Next
    With ThisWorkbook.Worksheets(strSheetName1)
        .UsedRange.ClearContents
        .Range(A1).Resize(UBound(varArrSheet1, 1), UBound(varArrSheet1, 2)) = varArrSheet1
    End With
    
End Sub

Regards
Prince

[David Hill]

Thank you for your replay Prince. I get the subscript out of range error at this line.

If varArrSheet1(lngCtr1, 5) = varArrSheet1(lngCtr2, 5) Then