CODEWITHSUNDEEP
vbscript
user1942137
user1942137

Reputation: 1

VB Script Space

I am in need of a vb script that will be used as a macro in excel.

I need the rows with data that have spaces to have the spaces removed. I know how to create a formula one cell at a time but I am clueless on how to do this on a large scale with a looping script. Any help would be appreciated.

Upvotes: 0

Views: 309

Answers (2)

Amol Chavan
Amol Chavan

Reputation: 3970

Don't know that much about the micros, but in vb-script you can use following code :-

Option Explicit 
Dim objFso,strFileName,objFile
Set objFso = CreateObject("Scripting.FileSystemObject")          
strFileName="C:\Documents and Settings\amolc\Desktop\test.txt"   ''Path of text file

Set objFile = objFso.OpenTextFile(strFileName,1)
strText = objFile.ReadAll
objFile.Close
strText = Replace(strText," ","",vbTextCompare)            ''Replace function to remove space
Set objFile = objFso.OpenTextFile(strFileName,2)
objFile.Write (strText)
objFile.Close

Upvotes: 1

Reinstar
Reinstar

Reputation: 146

Try this code:

Columns("F:F").Select

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

I try to replace character space (" ") with none ("")

Upvotes: 0

Related Questions