Clear data in a row when certain text appear in a cell

[kelly mort]

Hello world,

I am stacked here with a challenge and I need your help pull me out:


I have a column that contains list of items so I need a script that looks for a specific item in that column say "Book" and when found, clear data for the row that contains the item "book".

I am just confused, how to write the loop.


My table starts from A7 to Q100 and the column with the list of items is column C

Thanks in advance
Kelly

 

[Fluff]

How about

Sub delrws()
   With Range("C7", Range("C" & Rows.Count).End(xlUp))
      .Replace "Book", "=XXX", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   End With
End Sub

 

[kelly mort]

Okay thanks.

How do I point it to the sheet i want it to handle?

Say Sheet1

 

[Fluff]

Like this

Sub delrws()
   With Sheets("Sheet1").Range("C7", Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp))
      .Replace "Book", "=XXX", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   End With
End Sub

 

[kelly mort]

Oh nice, it's working nice now. Thanks again

But can you explain the script to me a bit?


Regards
Kelly

 

[Fluff]

Glad to help & thanks for the feedback.

Sub delrws()
   With Sheets("Sheet1").Range("C7", Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp))
      .Replace "Book", "=XXX", xlWhole, , False, , False, False   ' Replaces Book with =XXX which causes a formula error
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete        ' Finds all cells with a formula error & deletes that row
   End With
End Sub

 

[kelly mort]

Wow, very brilliant! !!!

Have a nice time