VBA - Comparing values in lists


New Member
Jan 18, 2022
Office Version
  1. 365
  1. Windows
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.



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

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)

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.

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi & welcome to MrExcel.
How about
VBA Code:
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
Upvote 0

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.
Upvote 0
Did you copy paste the code, or did you type it out?
Upvote 0
I did typed it out rather than copy paste - but have double/triple checked I dotted the t's and crossed the i's....
Upvote 0
In that case try copy/pasting the code.
I suspect that you missed the period in front of one the the Ranges.
Upvote 0
Must be something else have not missed the periods in front of the Ranges, everything looks to be the same as your example.
Upvote 0
Okay - yes it was a typo :)

Thank you for the help, I have a different error now, but I will try figure it out.
Upvote 0
You're welcome & thanks for the feedback.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back