Delete rows of continuous name if value matches input box

[firmament1987]

i did a for loop if column Purchase item contains "Apple" it will delete it and move on however i want it to also delete all item within customer A not just his "apple" row leaving behind banana row and pear row

so outcome will be only customer C - Papaya and Customer D - pineapple will remain all other rows will be deleted

Please help thanks in advance

Name	Purchased item
Customer A	Apple
Customer A	Banana
Customer A	Pear
Customer B	Apple
Customer C	Papaya
Customer D	Pineapple

 

[Rick Rothstein]

Does this macro do what you want...

Sub ClearCustomerIfPurchasedApples()
  Dim R As Long, LastRow As Long, ItemToFind As String
  ItemToFind = "apple"
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  For R = 2 To LastRow
    If LCase(Cells(R, "B")) = ItemToFind Then
      Range("A2:A" & LastRow) = Evaluate(Replace("IF(A2:A#="""","""",IF(A2:A#=""" & Cells(R, "A").Value & ""","""",A2:A#))", "#", LastRow))
    End If
  Next
  Range("A2:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[firmament1987]

Hi! Thanks for your help, have been stuck on it for abit it does clear off customer B(which i was able to achieve) but i want it to also clear away all customer A's order so kind of if within customer's order there is an "apple" remove all orders within the same customer's name.

Thanks!

 

[Rick Rothstein]

The code I posted did what you are asking for using the sample you posted... I tested it before I posted it. If you are not getting it to work for you, then you will need to post that workbook (obfuscate any sensitive data) to a safe file sharing service (like Drop Box) so we can test our solutions against the actual data that you are working with.

 

[Michael M]

Is the text exactly "Apple"....or are some of them "apple" or "APPLE"