To clear apostrophes

[Guest]

Hello,
I've used a variation of the following code to clear zeros from a data
range. Some data that I've imported contains a combination of dates, numbers
and text but mysteriously some cells appear blank but on inspection of the
formula bar they contain a solitary apostrophe. I would have thought that the
following code should clear the apostrophes but it doesn't. Could anybody
help please

Sub CleanApostrophe()
Dim rng As Range, cell
Set rng = Range("Datarange2")
For Each cell In rng
If cell.Value = " ' " Then
cell.ClearContents
End If
Next cell
End Sub

 

[Chip Pearson]

Ben,

Try something like the following:

For Each cell In rng
If cell.PrefixCharacter = "'" And _
Len(cell.Value) = 0 And _
cell.HasFormula = False Then
cell.ClearContents
End If
Next cell


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

I like to do this:

select the range
edit|replace
what: (leave blank)
with: $$$$$ (some unique string)
replace all

followed by:
edit|replace
what: $$$$$ (that same unique string)
with: (leave blank)
replace all

If you need a macro, record one when you do it manually.

(I bet those apostrophes are left over from a formula that returned "" and you
converted to values???)