Removing dashes in entire column using VBA

[sup191]

I posted here earlier and got very quick, helpful answers and thought
might try it one more time... I'm trying to format a whole column s
that when data is pasted in it, all the dashes are removed. Fo
example, I want anything pasted into column K to have the dashe
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 peopl
who are using it don't have to do that extra step. My job consists o
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 ca
pop in are dashes which screw up the program I'm importing thi
spreadsheet into.

So, "qwert-123" would autoformat to "qwerty123".

Is this possible without copying a substitute function to every cell i
the column?

Thanks in advance for any help solving this puzzle. ;

 

[Gord Dibben]

sup

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then ''1 is column A''
Target.Cells.Replace What:="-", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
enditall:
Application.EnableEvents = True
End Sub

Copy to a worksheet module(right-click on sheet tab and "View Code") and paste
in there.

Gord Dibben Excel MVP

 

[Jim Feaver]

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]

Thank you so much Gord and Jim!! I still getting a grasp on all thi
VBA stuff and you've both given me a couple ways to achieve my goal.
love learning this stuff...

I only hope I can understand it enough to be of some help to people i
need as both of you have been to me.

Thanks again guys!
sup19

 

[Jim Feaver]

Hey 'Sup:

I followed Gord's lead and added some error trapping code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup
Application.EnableEvents = False
If Target.Column = 11 Then
RemoveDashes
End If
Cleanup:
Application EnableEvents = True
End Sub

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]

I updated the code and everything seems to be working smoothly. Thank
again!

I do have one question though and don't know if it can be done. Th
removal of dashes works perfectly for data entered directly into colum
K, or pasted into column K. However, it doesn't remove the dashes whe
doing a block paste. If I try pasting columns J & K at the save time
the dashes remain in column K. I'm assuming that column J is th
active column since I put the cursor on J1 and paste - column K get
filled in, but since it's not the "active" column, it isn't being see
by the code.

Is that sorta right? Do I make sense? This stuff is going to age m
quickly, but it's so much fun!! ;

 

[sup191]

mid-afternoon *bump*

 

[Jim Feaver]

Hi:

Here is a revised Worksheet_Change event procedure.
And paste that involves column K will call
the RemoveDashes procedure.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup
If Not (Intersect(Target, Range("K:K")) Is Nothing) Then
Application.EnableEvents = False
RemoveDashes
End If
Cleanup:
Application.EnableEvents = True
End Sub

hth,
Jim Feaver

 

[sup191]

Perfect. Thanks again Jim. You're making me look like a wiz with th
people at work. But don't worry... I keep telling them all my help i
coming off this forum! ;

 

[Jim Feaver]

Thanks alot. Nice to hear you're basking in
adulation. Milk it, baby!
I've been getting help in this group for years.
My code is gradually getting less clunky.
Mostly I rely on the Google archive of this and other ngs.
Jim