Remove leading spaces from excel worksheet

[ir26121973]

Hi,

Wonder if somone can help me please.

I have a spreadsheet with a number of cells that are blank to look at but
have a space in them as the first character.

Can someone tell me please how to write a macro that within a given range
looks at the cells that look blank and have the first character as a space
and then remove that space.

Many thanks

Chris

 

[Tom Ogilvy]

sub RemoveApparentBlank()
Dim rng as Range, cell as Range
On error resume Next
set rng = Selection.SpecialCells(xlFormulas,xlTextValues)
On Error goto 0
if not rng is nothing then
for each cell in rng
if len(trim(cell)).Value = 0 then
cell.clearcontents
end if
Next
end if
set rng = Nothing
On error resume Next
set rng = Selection.SpecialCells(xlConstants,xlTextValues)
On Error goto 0
if not rng is nothing then
for each cell in rng
if len(trim(cell)).Value = 0 then
cell.clearcontents
end if
Next
end if
end sub

 

[Sandy]

Try this: just highlight the cell you want checked and run the macro

Sub RemoveSpaces()
Dim MyCell As Object
For Each MyCell In Selection
MyCell.Value = Trim(MyCell)
Next MyCell
End Sub

Sandy

 

[ir26121973]

Tom,

Thanks for this. I've put the code in VB but there seems to be an error in
this line.

if len(trim(cell)).Value = 0 then

VB highlights it in red and says it expects a Then or Goto.

Any advice please?

Thanks

Chris

 

[Sandy]

Try this: just highlight the cell you want checked and run the macro

Sub RemoveSpaces()
Dim MyCell As Object
For Each MyCell In Selection
MyCell.Value = Trim(MyCell)
Next MyCell
End Sub

Sandy

 

[ir26121973]

Thanks for this Sandy.

Where would I put in a specific range as the spreadsheet does have quite a
lot of information in.

Thanks

Chris

 

[Dave Peterson]

A small typo:

if len(trim(cell.Value)) = 0 then

 

[Sandy]

Replace "Selection" with your range ie Range("A1100")

 

[ir26121973]

Sandy thanks for this, works a treat.

Regards

Chris