Clearing cells that only contain spaces

[Guest]

Hi all!

Can anyone tell me how to write code to remove any number of spaces from cells that ONLY contain spaces?

Thank you!

Rodrigo

 

[Ken Wright]

Dave McRitchie has already done it for you. Use his TrimAll macro:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

This will though clear duplicated spaces in cells with other data, so if that is
NOT what you want then don't use it, though you didn't spefically exclude it.
Just post back if not.

 

[Tom Ogilvy]

Dim rng as Range, cell as Range
On Error Resume Next
set rng = ActiveSheet.UsedRange.SpecialCells(xlconstants,xlTextValues)
On Error goto 0
for each cell in rng
if len(trim(cell.Value)) = 0 then
cell.Clearcontents
end if
Next

--
Regards,
Tom Ogilvy

Hi all!

Can anyone tell me how to write code to remove any number of spaces from

cells that ONLY contain spaces?