Clear cell that contains a certain value

[yoshi88]

Hi I am trying to use a code to clear cells if their value is the same as the P1 cell. The if part doesn't seem to work and I can not find what is wrong.

I dont want to use the find and replace option in Excel.

Range("D1").Select
Do Until Range("D50").Select
If ActiveCell.Value = ("P1") Then
ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

Thanks

Frank

 

[KenWright]

Various ways of dealing with this, but here are a couple:-

Sub ClearCells()

Dim r As Long
Dim LastRow As Long
Application.ScreenUpdating = False

LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
For r = 1 To LastRow
If StrComp(Cells(r, "D"), Range("P1"), vbBinaryCompare) = 0 Then
Cells(r, "D").ClearContents
Else: End If
Next r

Application.ScreenUpdating = True
End Sub
Sub ClearCells2()

Dim r As Long
Dim LastRow As Long
Dim cel As Range
Application.ScreenUpdating = False

Set Comp = Range("P1")
Set Rng = Range("D150")

For Each cel In Rng
If StrComp(cel, Comp, vbBinaryCompare) = 0 Then
cel.ClearContents
Else: End If
Next cel

Application.ScreenUpdating = True
End Sub

Always try and avoid selecting if possible.

Regards
Ken.....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------