Remove leading spaces from excel worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top