Delete space in Text

[herve]

Hello,
does anyone know a macro that could remove the blanks or Space in cell
C11 to C2000 (where there is text) Ex. 20 15 10
Thanks in advance.

 

[dominicb]

Good evening herve

This cheeky litle macro should do the trick.

Sub RemoveSpaces()
For Each UsrCell In Selection
UsrCell.Value = Application.WorksheetFunction.Substitute(UsrCell.Value,
" ", "")
Next UsrCell
End Sub

Highlight the range you want it to effect before running it. That way,
it will work on any range you later want to specify, not just
C11:C2000.

HTH

DominicB

 

[herve]

hello Domincd
thanks for the reply but could you tell me how to do it only for th
cell C11 to C500
without having to do a selection?
Thank

 

[dominicb]

Hi herve

This will do the trick:

Sub RemoveSpaces()
Range("C11:C500").Select
For Each UsrCell In Selection
UsrCell.Value = Application.WorksheetFunction.Substitute(UsrCell.Value,
" ", "")
Next UsrCell
End Sub

HTH

DominicB

 

[Dave Peterson]

How about selecting C11:C2000 and then
edit|Replace
what: (spacebar)
with: (leave blank)
replace all

If you need it as a macro, you can record it when you do it manually.