VBA - Comparing values in lists

[Nibblesy]

Hi all, hope you can help.

I have two lists, List A (a small List) and List B (a very large List).
List B contains everything in List A
I need to iterate through List A - getting each value from a range - column ("A2", "A" & LastRow) - where LastRow is the row number of the last populated row in List A. Then compare that to each value in column A of List B.
If they match, then delete that entire row from List B.
And continue this until all values in List A have been checked against List B.

The ranges of the datasets in both lists will change depending on the source data imported, so I have code to get the Last Row Numbers of each, that's fine, just having trouble iterating through and getting that active cells to compare and do the 'if' 'then'.

Basically just deleting the rows contained in the small list out of the big list, but the list sizes will change, so don't have a set range size for each list.

Hope this makes sense?
Any help with some code examples would be much appreciated.

:D

Dims....etc

' Get LastRow Values
Worksheets("List_B").Activate
Set Sht = ActiveSheet
LastRowB = Sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
LastRowB = LastRowB + 0

Worksheets("List_A").Activate
Set Sht = ActiveSheet
LastRowA = Sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
LastRowA = LastRowA + 0


' Iterate through List B & check each column A cell value against the cell values in List B and remove from List B if a match is found
Set iRangeINC = Sht.Range("A2", "A" & LastRowA)
Worksheets("List_B").Activate

For Each iCells In iRange
LastRowI = Worksheets("List_B").Range("A" & Rows.Count).End(xlUp).Row
For i = LastRowI To 1 Step -1
var1 = ActiveCell.Row
var2 = Worksheets("List_B").Range("A" & i).Value
If var1 = var2 _
Then Worksheets("List_B").Rows(i).EntireRow.Delete
Next i
Next iCells


- Is it something to do with the row number for the delete? The above runs and doesn;t return anything in the debugger. Forgive, I'm new to VBA.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Nibblesy()
   Dim Dic As Object
   Dim Cl As Range, Rng As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("List_A")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("List_B")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[Nibblesy]

Hi,

Thank you for the speedy reply

I will look into Dictionary objects for sure.

With the example you gave above I get a Run-time error 1004 - application-defined or object-defined error on the first For Each line
For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))

I guess maybe for the .Range
I'll see if I can get it to work.


Again, thank you for your time, appreciate it.

 

[Fluff]

Did you copy paste the code, or did you type it out?

 

[Nibblesy]

I did typed it out rather than copy paste - but have double/triple checked I dotted the t's and crossed the i's....

 

[Fluff]

In that case try copy/pasting the code.
I suspect that you missed the period in front of one the the Ranges.

 

[Nibblesy]

Must be something else have not missed the periods in front of the Ranges, everything looks to be the same as your example.

 

[Nibblesy]

Okay - yes it was a typo

Thank you for the help, I have a different error now, but I will try figure it out.

 

[Fluff]

You're welcome & thanks for the feedback.