Excel marco help

[Mark M]

I know my way around Excel but never ventured into any macro or VBA stuff so thought why not give it a whirl and see if I can speed up some tasks I do in Excel.

If possible I would like to run a macro on a worksheet that will automatically find and delete a series of phrases eg:

"Direct credit from "
"Debit card payment to "
"On-line Banking bill payment to "
Etc

NB - The trailing space at the end is important and needs to be deleted.

Currently I am just using find and replace within Excel and obviously replacing them with nothing so it deleted them.

Can a macro do this for multiple phrases? What happens if a certain phrase isn't on this particular spreadsheet on a particular occasion?

Thanks, Mark

 

[gillywibble]

Insert the following as a module:-

Sub Replace()

Cells.Select
Selection.Replace What:="Direct credit from ", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Debit card payment to ", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="On-line Banking bill payment to ", Replacement _
:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End Sub

If the phrase isn't on the spreadsheet, the macro will still run.

 

[EyepodI]

Yes you can do this with a macro.

If your trying to learn Excel macros to do this then the easiest way would be to record a macro whilst doing the Find / Replace manually and modify the macro to your specific needs.

Sub Macro1()
Selection.Replace What:="Direct credit from ", Replacement:=""
Selection.Replace What:="Debit card payment to ", Replacement:=""
Selection.Replace What:="On-line Banking bill payment to ", Replacement:=""
End Sub

That very basic macro would do what you need, assuming you have selected the range containing the phrases before hand and the data is in rows. If it doesn't find the string it won't do anything. Include the trailing space in the "What:=" string.

If you record the macro manually you'll see that there are a lot more options for the Replace command that may be relevant to your needs.