THEsewingmaster
New Member
- Joined
- Mar 14, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Hello,
I'm new here! I've been scouring the web for a vba that would work for my application and found one. I adapted it to the list configuration I needed, but am having 1 problem: IT IS SLOW (I mean slooowww)! At the moment, I'm only comparing about 60 or so data entries against a list of 10.
So here is the scenario:
Comparing 2 sheets
Here is the VBA I'm using:
Any help in a better solution that accomplishes this, would be much appreciated!!
I'm new here! I've been scouring the web for a vba that would work for my application and found one. I adapted it to the list configuration I needed, but am having 1 problem: IT IS SLOW (I mean slooowww)! At the moment, I'm only comparing about 60 or so data entries against a list of 10.
So here is the scenario:
Comparing 2 sheets
- R_STATUS_LIST
- DATA
- starting in C2, then C column for R_STATUS_LIST
- starting in F5, then F column for DATA
Here is the VBA I'm using:
VBA Code:
Sub CompareLists2()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim Rng As Range, RngList As Object
Set RngList = CreateObject("Scripting.Dictionary")
With Sheets("DATA")
For Each Rng In .Range("F5", .Range("F" & .Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next
End With
With Sheets("R_STATUS_LIST")
For Each Rng In .Range("C2", .Range("C" & .Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
Sheets("DATA").Cells(Sheets("DATA").Rows.Count, "F").End(xlUp).Offset(1, 0) = Rng
End If
Next
End With
RngList.RemoveAll
Application.ScreenUpdating = True
End Sub
Any help in a better solution that accomplishes this, would be much appreciated!!