Removing dashes in entire column using VBA
 

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. ;

--
Message posted from http://www.ExcelForum.com

Removing dashes in entire column using VBA
 

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

On Wed, 4 Feb 2004 21:48:15 -0600, sup191
wrote:

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/

Removing dashes in entire column using VBA
 

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/

Removing dashes in entire column using VBA
 

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

--
Message posted from http://www.ExcelForum.com

Removing dashes in entire column using VBA
 

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 " wrote in message
...
Thank you so much Gord and Jim!! I still getting a grasp on all this
VBA stuff and you've both given me a couple ways to achieve my goal. I
love learning this stuff...

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

Thanks again guys!
sup191


---
Message posted from http://www.ExcelForum.com/

Removing dashes in entire column using VBA
 

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!! ;

--
Message posted from http://www.ExcelForum.com

Removing dashes in entire column using VBA
 

mid-afternoon *bump*


---
Message posted from http://www.ExcelForum.com/

Removing dashes in entire column using VBA
 

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 " wrote in message
...
I updated the code and everything seems to be working smoothly. Thanks
again! :)

I do have one question though and don't know if it can be done. The
removal of dashes works perfectly for data entered directly into column
K, or pasted into column K. However, it doesn't remove the dashes when
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 the
active column since I put the cursor on J1 and paste - column K gets
filled in, but since it's not the "active" column, it isn't being seen
by the code.

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


---
Message posted from http://www.ExcelForum.com/

Removing dashes in entire column using VBA
 

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! ;

--
Message posted from http://www.ExcelForum.com

Removing dashes in entire column using VBA
 

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

"sup191 " wrote in message
...
Perfect. Thanks again Jim. You're making me look like a wiz with the
people at work. But don't worry... I keep telling them all my help is
coming off this forum! ;)


---
Message posted from http://www.ExcelForum.com/