VBA Help Needed-Removing Hyphens From A Column (Worksheet_Change)

[milwphil]

Good afternoon!

I'm trying to come up with code and keep the same type of method consistent with the spreadsheet that I inherited. I've had a lot of luck with this forum, thanks to JonMo1 and others, with a similar issue and I was hoping I'd have the same luck with this issue:

I'm trying to come up with code to remove 'hyphens' in only one column (column I) in a loop (not the entire worksheet).

To be consistent with the methods, I was playing around with WorksheetFunction.Replace (not even sure that's the best function to use- what about WorsheetFunction.Substitute?), but couldn't figure out how to define the range to column I (named "License").

An example of the loop used in the spreadsheet:

For Each oCell In Target
    oCell.Value = WorksheetFunction.Trim(oCell.Value)
Next oCell

I was hoping for something like:

For Each oCell In Target
    oCell.Value = [COLOR=red]WorksheetFunction.Replace "-" with "" (in column I or "License") ?????? -I wrote this just as an example (not that I thought it would work in vba)[/COLOR]
Next oCell

THANKS FOR YOUR HELP IN ADVANCE!

 

[CWatts]

Cell by cell will be slow, but you can use the "find replace" built into excel in vba with the following line:

Sub HyphenReplacement()
     Columns("I:I").Replace What:="-", Replacement:=""
End Sub

 

[pboltonchina]

Try this

Columns("I").Replace What:="-", Replacement:="", _
        LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False

 

[milwphil]

WOW! Thanks for the quick responses!

pboltonchina,
Where would I put in the Worksheet_Change code?:

Columns("I").Replace What:="-", Replacement:="", _
LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False

CWatts,
I haven't had a chance to try your method yet, but will let you know my results.

THANKS AGAIN!!!!!

 

[milwphil]

Scratch that last question! I got it!

THIS FORUM IS AMAZING!

pboltonchina... I added your code after a loop in the WorkSheet_Change and it worked amazingly! It's rare that I get things on the first shot. I can screw up the most basic instructions sometimes!

Thank you CWATTS and PBOLTONCHINA for your responses!