How to clear cell contents based on a criteria

[m_aatif]

I have a range from A1 to F4.

I want to clear contents of the cells in range if is is other than "B"

Whats wrong eith following code?

Sub Deletecell()
Dim R As Range
Set R = Worksheets("sheet1").Range("A1:F4")
For Each Cell In R
If Cell.Value <> "B" Then
Cell.Value.ClearContents
Next
Else
End Sub

 

[VoG]

Try

Sub Deletecell()
Dim R As Range, Cell As Range
Set R = Worksheets("sheet1").Range("A1:F4")
For Each Cell In R
    If Cell.Value <> "B" Then Cell.ClearContents
Next Cell
End Sub

 

[Rick Rothstein]

This non-looping one-liner macro should also work for you...

Sub ClearNonBsInA1F4()
  Worksheets("Sheet1").Range("A1:F4") = Evaluate("IF(Sheet1!A1:F4<>""B"","""",Sheet1!A1:F4)")
End Sub