Parse space

[isleofman]

I'm having a small problem with a VB script which parses two words into one single word. It works for the first row but not the following rows.

Can anybody please help me - I think the answer is looking right at me!?!

Sub Remove()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Offset(, 1).Value = Replace(.Value, " ", "")
    End With
Next i
End Sub

 

[MrKowz]

It works fine for me, can you please post a sample of your data?

Also, you could take care of this without a macro - do the following and then copy/paste special the results:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">John Jacob</td><td style=";">JohnJacob</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Jingelheimer Schmidt</td><td style=";">JingelheimerSchmidt</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">His Name</td><td style=";">HisName</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Was My</td><td style=";">WasMy</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Name Too</td><td style=";">NameToo</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">A1," ",""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Richard Schollar]

If it doesn't appear to work it may be because you don't have a 'normal' space in the cell but rather a non-breaking space - these are ascii character 160 rather than 32.

Test by using Keith's formula:

=SUBSTITUTE(A1,CHAR(160),"")

and see if this correctly removes these 'spaces' that weren't previously removed

 

[kpark91]

Or you could easily select all the ranges you want to parse
and press Ctrl + H
and fill in the blanks as provided (replace with, replace)
and then just replace them all