How to delete cells with a given data?
 

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.

How to delete cells with a given data?
 

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"!
--
Gary''s Student - gsnu201001


"Teka" wrote:

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.

How to delete cells with a given data?
 

Use Edit Replace and replace with nothing. E.g "Cat" part cell match.


--
Regards
Dave Hawley
www.ozgrid.com
"Teka" wrote in message
...
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.

How to delete cells with a given data?
 

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



Teka wrote:

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.

--

Dave Peterson

How to delete cells with a given data?
 

EditFindFind All

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

EditDelete or EditClear Contents or your choice.


Gord Dibben MS Excel MVP


On Fri, 2 Apr 2010 01:03:01 -0700, Teka
wrote:

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.