Hi all,

I'm trying to write a macro that looks in a given column of data for two
asterisks (**), for instance. If this macro encounters a cell with **
anywhere within its contents, it should delete the entire contents of
the cell including the asterisks. I need the desired column of data to
be user defined as well.

Does anyone know how I would go about creating this?

Any advice would be appreciated.

Many thanks,

Kelly



*** Sent via Developersdex http://www.developersdex.com ***

To be clear, I don't want to delete the cell - only the contents within
the cell.

My title is misleading - apologies.



*** Sent via Developersdex http://www.developersdex.com ***

You could record a macro when you do:
Select the column
Edit|Replace
what: *~*~**
with: (leave blank)
replace all


This is what the code would look like to search for ** in the entire column of
the activecell.

ActiveCell.EntireColumn.Replace _
What:="*~*~**", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Since * serves as a wild card, you specify an "escape" character telling excel
that you really mean the asterisk (~*).

So *~*~**
is
(wildcard)(real asterisk)(real asterisk)(wildcard)

The same technique works when you want to change (or find) a question mark: ~?

(and ~~ for a real tilde)

Kelly wrote:

Hi all,

I'm trying to write a macro that looks in a given column of data for two
asterisks (**), for instance. If this macro encounters a cell with **
anywhere within its contents, it should delete the entire contents of
the cell including the asterisks. I need the desired column of data to
be user defined as well.

Does anyone know how I would go about creating this?

Any advice would be appreciated.

Many thanks,

Kelly

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson