For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?

Sub RemoveText()

With ActiveSheet.Cells
Set c = .Find(what:="text", _
LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
fFrstAddr = c.Address
Do
c.ClearContents

Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And _
c.Address < FirstAddr
End If
End With


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145555

try this

Sub cleaner()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If InStr(1, rng, "text", vbTextCompare) 0 Then
rng.Value = Clear
End If
Next
End Sub


On Oct 19, 2:27*pm, ppeer wrote:
For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?

The find method I used will run much quicker especially if the used area
is large.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145555

Check your other post.

ppeer wrote:

For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?

--

Dave Peterson

I'm pretty sure this single line of code will do what you want...

Cells.Replace "*text*", "", xlPart, xlByColumns, False

--
Rick (MVP - Excel)


"ppeer" wrote in message
...
For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?

I didn't see a previous post by this person.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
Check your other post.

ppeer wrote:

For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?

--

Dave Peterson

Check...

From: ppeer
Newsgroups: microsoft.public.excel
Subject: delete cell if part of cell contains
Date: Mon, 19 Oct 2009 01:56:02 -0700 (PDT)


Rick Rothstein wrote:

I didn't see a previous post by this person.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
Check your other post.

ppeer wrote:

For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?

--

Dave Peterson

--

Dave Peterson

Ahh, okay, I see. Anyway, I still think either the code line I posted or the
equivalent Replace All from the menu bar would be good approach.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
Check...

From: ppeer
Newsgroups: microsoft.public.excel
Subject: delete cell if part of cell contains
Date: Mon, 19 Oct 2009 01:56:02 -0700 (PDT)


Rick Rothstein wrote:

I didn't see a previous post by this person.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
Check your other post.

ppeer wrote:

For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?

--

Dave Peterson

--

Dave Peterson

On 19 okt, 19:48, "Rick Rothstein"
wrote:
I'm pretty sure this single line of code will do what you want...

Cells.Replace "*text*", "", xlPart, xlByColumns, False

--
Rick (MVP - Excel)

"ppeer" wrote in message

...



For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

thanks, this line of code gets the job done!

On 19 okt, 19:48, "Rick Rothstein"
wrote:
I'm pretty sure this single line of code will do what you want...

Cells.Replace "*text*", "", xlPart, xlByColumns, False

--
Rick (MVP - Excel)

"ppeer" wrote in message

...



For a worksheet I would like to check the cells if they contain the
word "text".
Within a cell this word is always combined e.g.: text 06735
When the cell contains "text" I would like to clean the whole cell, so
text including e.g: 06735.
Result: a blank cell.

Please, can somebody help me out?- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

and I like the minimalized code!