Marco that Delete Cells With Specific Text

[Chuong Nguyen]

Please help - I need a macro to clean up a worksheet

Look for in column "A" the word "Created:" then when it is located (even
with the whole string) it will delete the whole cell

Column A
1 Orig ER: Emilio N. Francisco (emilioef)
2 Created: 1/6/2008 6:45:00 AM Scheduled: Yes
3 Eff Date: 01/08/2008 Conf#: 1G5863K App: CLKCCM
4 Recipient: SHIRLEY HEPBURN (4045656) Send Prenote: No
5 SHIRLEY HEPBURN
6 Orig CR: Emilio N. Francisco (emilioef)
7 Created: 1/7/2008 6:44:00 AM Scheduled: No
8 Eff Date: 01/08/2008 Conf#: 1G5861B App: CLKCCM
9 Recipient: DOUGLAS ANDERSON (4043211) Send Prenote: Yes
10 DOUGLAS ANDERSON
11 Orig BR: Emilio N. Francisco (emilioef)
12 Created: 1/5/2008 6:52:00 AM Scheduled: Yes
13 Eff Date: 01/08/2008 Conf#: 1G5862L App: CLKCCM
14 Recipient: JOHN ZALESKY (100274) Send Prenote: No
15 JOHN ZALESKY


Any help would be appreciated.

Chuong Nguyen

 

[Rick Rothstein \(MVP - VB\)]

Does it have to be a macro? Select column A, click Edit/Find (or press Ctrl+F), type Created in the text field, click Find All, select all the rows in the list that is displayed (Ctrl+A will do that), Close the Find dialog and press Delete.

If it needs to be a macro, try this...

Sub ClearCreatedCells()
Dim X As Long
For X = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If InStr(Cells(X, "A").Value, "Created") Then Cells(X, "A").Value = ""
Next
End Sub

Rick

 

[Chuong Nguyen]

thanks so much

 

[Dave Peterson]

Another way if you really meant that you wanted to clear the contents of those
cells.

Record a macro when you:
Select column A
Edit|Replace
what: *Created:*"
with: (leave blank)
replace all

 

[Rick Rothstein \(MVP - VB\)]

Sigh! What was I thinking! Here I gave this longish, convoluted mechanical procedure instead of the simple, direct procedure you posted. Sometimes I have to wonder about myself. My only hope is that the OP was after the macro solution and didn't notice.<g> Anyway, thanks for following up this thread with your posting.

Rick

 

[Dave Peterson]

It's always nice to have options!
<bg>

Sigh! What was I thinking! Here I gave this longish, convoluted mechanical procedure instead of the simple, direct procedure you posted. Sometimes I have to wonder about myself. My only hope is that the OP was after the macro solution and didn't notice.<g> Anyway, thanks for following up this thread with your posting.

Rick