Hello,
I got the code below. With 24000 rows it takes 42 seconds to execute. Is it possible to do it in another way to speed up?
Kalle
I got the code below. With 24000 rows it takes 42 seconds to execute. Is it possible to do it in another way to speed up?
Kalle
Rich (BB code):
Rich (BB code):
Private Sub CommandButton5_Click()
Dim rng1 As Range
Dim strSearch As String
Dim V As Variant
Dim shRow As Long
NotCorrect = 0
startTime = Timer
' Speed on
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
' Set Range
N = Cells(Rows.Count, "A").End(xlUp).Row
RangeNew = "A1" & ":" & "A" & Str(N)
RangeNew = Replace(RangeNew, " ", "")
' Search Sheet2
Set rRng = Sheets("sheet1").Range(RangeNew)
For Each rCell In rRng.Cells
V = Application.Match(rCell.Value, Sheets("Sheet2").Range(RangeNew), 0)
If IsError(V) Then
Sheets("sheet1").Cells(rCell.Row, 1).Interior.Color = vbYellow
NotCorrect = NotCorrect + 1
Else
End If
Next rCell
' Speed off
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
EndTime = Timer
MsgBox "Total Time: " & EndTime - startTime
MsgBox Str(NotCorrect)
End Sub
