Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Script To replace space with nothing
Hey ,
is it possible to have a VB script that will search out a rang that i specified 2 elimnate spacing between text in a cell .. fo example .. first name and replace with firstname ? i have done this with a macro ... but i would like it as a script .. the for the macro was as follows : ' Macro1 Macro ' This is To Eliminate Spaces Within Headers ' ' Keyboard Shortcut: Ctrl+h ' Range("A1:Y1").Select Range("Y1").Activate ActiveWorkbook.Names.Add Name:="Headers", RefersToR1C1:= _ "=Sheet1!R1C1:R1C23" Selection.Replace What:="last digit", Replacement:="lastdigit" LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="first name", Replacement:="firstname" LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="last name", Replacement:="lastname" LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="job title", Replacement:="jobtitle" LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="toll free", Replacement:="tollfree" LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="post code", Replacement:="postcode" LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="id number", Replacement:="idnumber" LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False End Sub :confused -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Script To replace space with nothing
Jon,
Just go through the data and for each cell use Replace like replace(A1," ","") ' Macro1 Macro ' This is To Eliminate Spaces Within Headers ' ' Keyboard Shortcut: Ctrl+h ' ActiveWorkbook.WorkSheets("Sheet1").Range("A1:Y1") .Name = Headers" For Each cell In Range("Headers") Replace (cell," ","") Next cell End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "JonEx2k2 " wrote in message ... Hey , is it possible to have a VB script that will search out a range that i specified 2 elimnate spacing between text in a cell .. for example .. first name and replace with firstname ? i have done this with a macro ... but i would like it as a script .. the for the macro was as follows : ' Macro1 Macro ' This is To Eliminate Spaces Within Headers ' ' Keyboard Shortcut: Ctrl+h ' Range("A1:Y1").Select Range("Y1").Activate ActiveWorkbook.Names.Add Name:="Headers", RefersToR1C1:= _ "=Sheet1!R1C1:R1C23" Selection.Replace What:="last digit", Replacement:="lastdigit", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="first name", Replacement:="firstname", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="last name", Replacement:="lastname", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="job title", Replacement:="jobtitle", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="toll free", Replacement:="tollfree", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="post code", Replacement:="postcode", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="id number", Replacement:="idnumber", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
How to Replace ? With Space | Excel Worksheet Functions | |||
replace space | Excel Worksheet Functions | |||
How do I write a script to add space between numbers in a column? | Excel Discussion (Misc queries) | |||
Replace (') to space | Excel Worksheet Functions |