Bob, Brilliant, works a treat, easy when you know how!

Many thanks, rob
"Bob Phillips" wrote in message
...
The Len returns 0 so you have to use that creatively <G

Public Sub Test()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
If Len(cell.Value) = 0 Then cell.Value = cell.Value
End If
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Bob,

Thanks that removed the apostrophe however, it also has changed some data
for example '000045 becomes 45 whereas I need it to remain as a label as
there's a lookup formula that uses this.

Is there perhaps a second test to see it the length of the contents is
greater than zero. I try changing your code to use AND Len() but to no
avail.

Thanks, Rob

"Bob Phillips" wrote in message
...
Public Sub Test()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = cell.Value
End If
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rob" wrote in message
...
Hi,

I weekly receive a spreadsheet that's be created from a database
export
and
where there is no data (text or numbers), the cell contains an
apostrophe.

What I would like to do, is to check whether the cell only contains
and
apostrophe and if so, remove it. The range of data varies from week
to
week
so I'm guessing I need to select the range or somehow check the area
used
and run some VBA code.

I can recall something previously on the MS forums but searching
doesn't
find anything.

Thanks in advance. Rob