How to delete cells with a given data?

  • Thread starter Thread starter Teka
  • Start date Start date
T

Teka

I'm trying to delete series of cells with a given word inside, but there are
too many to FIND one by one and delete them.
 
This little macro will clear any cell that contains "sadness":

Sub MakeHappy()
Dim rr As Range, r As Range
Dim s As String, rClear As Range
Set rr = ActiveSheet.UsedRange
s = "sadness"
For Each r In rr
If InStr(r.Value, s) > 0 Then
If rClear Is Nothing Then
Set rClear = r
Else
Set rClear = Union(rClear, r)
End If
End If
Next
If rClear Is Nothing Then
Else
rClear.Clear
End If
End Sub

hopefuly it will make you "happy"!
 
What does delete mean?

1. Delete the contents of the cell?
2. Delete the cell (and move up or move left)?
3. Delete the entirerow?

If it's #1, you can:

Select the range
Edit|replace
What: *yourwordhere* (the asterisks are wild card. You want to include them.)
with: (leave blank)
replace all
 
Edit>Find>Find All

With the "found" dialog box open hit CTRL + a to select all "founds"

Edit>Delete or Edit>Clear Contents or your choice.


Gord Dibben MS Excel MVP
 
Back
Top