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

--
regards,
Tom Ogilvy

"ir26121973" wrote:

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

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 wrote:
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,

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

"Tom Ogilvy" wrote:

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

--
regards,
Tom Ogilvy

"ir26121973" wrote:

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

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 wrote:
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

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

"Sandy" wrote:

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 wrote:
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

A small typo:

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



ir26121973 wrote:

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

"Tom Ogilvy" wrote:

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

--
regards,
Tom Ogilvy

"ir26121973" wrote:

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

--

Dave Peterson

Replace "Selection" with your range ie Range("A1:D100")

ir26121973 wrote:
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

"Sandy" wrote:

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 wrote:
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

Sandy thanks for this, works a treat.

Regards

Chris

"Sandy" wrote:

Replace "Selection" with your range ie Range("A1:D100")

ir26121973 wrote:
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

"Sandy" wrote:

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 wrote:
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