Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing dashes in entire column using VBA
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Dashes | Excel Discussion (Misc queries) | |||
Cell Formatting Removing Dashes | Excel Discussion (Misc queries) | |||
Removing dashes from a column | Excel Discussion (Misc queries) | |||
removing dashes from numbers | Excel Worksheet Functions | |||
removing dashes from phone numbers | Excel Discussion (Misc queries) |