Hi,

I have two tabs - a tab called "Pricing" and a tab called "Funds to be removed"

I am trying to find all instances that occur within a cells in column H on the Pricing tab and delete that row if part of the cell is in column A on the "Funds to be removed tab.

For example: Funds to be Removed tab in column a would show "Jon" - in column H on Pricing there will be multiple instances of with the word "Jon" in column H like "12368_Jon", "6548244_Jon", etc

The code below is what I have written and seems to work but for some reason it does not delete all instances from the pricing tab. I wonder if there is something wrong with either the code or perhaps merged cells or blank rows are screwing up the code.

Any assistance would be greatly appreciate!


Dim WS1 as Worksheet
Dim WS2 as Worksheet

Set WS1 = Sheets("Pricing")
Set WS2 = Sheets("FUNDS TO BE REMOVED")

Sheets("FUNDS TO BE REMOVED").Select
Cells(2, 1).Select
TopRowRemove = ActiveCell.row
Columns("A").Find("", Cells(Rows.Count, "A")).Select
ActiveCell.Offset(-1, 0).Select
BottomRowRemove = ActiveCell.row

Sheets(("Pricing")).Select
Cells(11, 1).Select
TopRow = ActiveCell.row
LastRow = ActiveSheet.Range("B1").CurrentRegion.Rows.Count + 2

Application.Calculation = xlManual
 
For Each rcell In WS2.Range("A" & TopRowRemove & ":A" & BottomRowRemove)
    For Each cell In Sheets("Pricing").Range("H" & TopRow & ":H" & LastRow)
        If cell Like rcell Then cell.EntireRow.Delete
    Next cell
Next rcell

Application.Calculation = xlAutomatic