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

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
:confused:


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