delete cell if part of cell contains

P

ppeer

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?
 
J

joel

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 Su
 
M

muddan madhu

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
 
D

Dave Peterson

Check your other post.
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?
 
R

Rick Rothstein

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

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

Rick Rothstein

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.
 
P

ppeer

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

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

--
Rick (MVP - Excel)







- Tekst uit oorspronkelijk bericht weergeven -

thanks, this line of code gets the job done!
 
P

ppeer

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

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

--
Rick (MVP - Excel)







- Tekst uit oorspronkelijk bericht weergeven -

and I like the minimalized code!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top