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.
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.