Delete space in Text

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.

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

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

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

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.

herve wrote:
>
> 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.
>
> --
> herve
> ------------------------------------------------------------------------
> herve's Profile: http://www.excelforum.com/member.php...o&userid=27314
> View this thread: http://www.excelforum.com/showthread...hreadid=473046

--

Dave Peterson