Hi:
This procedure runs from Worksheet_Change event procedure.
'edited Worksheet_Change event procedure
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 11 Then
Application.EnableEvents = False
RemoveDashes
Application.EnableEvents = True
End If
End Sub
'place in a standard module
Sub RemoveDashes()
Dim lLastRow As Long
Dim Sh1 As Worksheet, rng As Range
Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
lLastRow = Sh1.Cells(Rows.Count, "K").End(xlUp).Row
Set rng = Sh1.Range("K1", "K" & lLastRow)
rng.Replace _
What:="-", Replacement:="", _
SearchOrder:=xlByRows, MatchCase:=True
Set Sh1 = Nothing
Set rng = Nothing
End Sub
HTH,
Jim Feaver
"sup191 " wrote in message
...
I posted here earlier and got very quick, helpful answers and thought I
might try it one more time... I'm trying to format a whole column so
that when data is pasted in it, all the dashes are removed. For
example, I want anything pasted into column K to have the dashes
removed automatically. I know I could just do a replace on column K,
but I'm trying to get this spreadsheet entirely automated so the people
who are using it don't have to do that extra step. My job consists of
TONS of copying and pasting and I was hoping for an automatic answer.
The data could be any alpha or number string. The only thing that can
pop in are dashes which screw up the program I'm importing this
spreadsheet into.
So, "qwert-123" would autoformat to "qwerty123".
Is this possible without copying a substitute function to every cell in
the column?
Thanks in advance for any help solving this puzzle. ;)
---
Message posted from http://www.ExcelForum.com/