Find text and clear all text from cell

[DamageZ]

Hi Guys & Girls,

I have a spreadsheet that contains various many cells with text contained in them.

I want to be able to find all cells that contain "P001" and clear the entire cell, not just the "P001".

For example I might have 3 cells (see below), I would want a macro to clear the 2 cells (top & bottom) based on the criteria that they contained "P001"
P001-20-0
P002-0-45
P001-10-0

I'm sure there is a simple way to do it, I just can't seem to find it.

Any suggestions or answers would be great!

Thanks,
Adam.

 

[Michael M]

Do you just want to clear cell or DELETE the entire row ??

Have to leave the office for a while...
This will clear the contents of the cell, but won't delete rows.

Sub MM1()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    If InStr(c.Value, "P001") Then
        c.ClearContents
    End If
Next c
End Sub

 

[Rick Rothstein]

If you only want to clear the cells containing "P001", then you can do that with this single line of code...

Cells.Replace "*P001*", ""

Using Cells the way I did will make the code line search all cells on the worksheet (I beleive within the UsedRange)... you can restrict that to one of more columns, of a definite range, by replacing Cells with a range reference covering the columns or cells you want to restrict it to.

 

[DamageZ]

Hi guys,

Thanks for your quick response. Yes, I just wanted to clear and not delete, thanks for checking.

Rick, your response makes me feel a little foolish . I should have know that, thank you very much for your solution.

Cheers,
Adam.