Help: VBA to check for a word in a range then delete it where it is similar?

[peter_z]

Hey Guys,

I am wondering if anyone knows how to write a simple code to check a range and delete if there are cells with a certain word i.e

Range

| Blue | Yellow |
| Blue | Black |


I want to delete all the cells like the word Yellow.

Thanks a lot for your help in advance

 

[Trebor76]

Hi there,

Try this:

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?t=560366
    'Clear cell contents like the word 'Yellow'
    
    Dim rngCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Range("A2:D50") 'Change to suit
        If rngCell.Value Like "*Yellow*" Then
            rngCell.ClearContents
        End If
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Michael M]

Hi Peter
Are you sure you want to "Delete" the cells and not just clear them ?
This will clear the cells that contain the text "Yellow"

Sub remtext()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection
    If c.Value = "Yellow" Then
     c.ClearContents '.Delete Shift:=xlUp
     End If
Next c
Application.ScreenUpdating = True
End Sub

To actually delete the cells, the code after "ClearContents" should be used, but it will shift all the cells up a row....which is gonna screw with your data.

 

[peter_z]

Nice one cheers guys code works exactly how I need it to.

You are right Michael I need to clear it not delete, Thanks

 

[Rick Rothstein]

This should be quite a bit faster than looping through each cell...

Selection.Replace What:="Yellow", Replacement:="", LookAt:=xlWhole, MatchCase:=True

You didn't specify a range, so I assumed like Michael that you would select the range manually... if the range is fixed, just substitute it for the Selection keyword. Also, you didn't say whether the word should be all that is in the cell or if other text could be in the cell with it... I assumed Yellow was the only text in the cell; nor did you say whether you wanted an exact match by letter casing or not... I assumed an exact letter case match. Those two items are controlled by the LookAt argument (I assigned xlWhole, the other choice is xlPart) and the MatchCase argument (I assigned True, the other choice is False).

 

[Michael M]

You could have also done a Find and Replace, which may have been easier than a code solution

 

[Rick Rothstein]

You could have also done a Find and Replace, which may have been easier than a code solution

Just a simple Edit/Replace (using the Replace All button) is all that is needed... the VB statement I posted is doing that in code.