Hi Anders,

Your problem is that the blank spaces are not spaces but so-called non
breaking space characters (chr(160)).

I have therefore replaced the find and replace macro with:

'=============
Public Sub FindAndRemoveBlanks()
ActiveSheet.UsedRange.Replace What:=Chr(160), _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub
'<<=============

I have sent you an updated workbook.


---
Regards,
Norman



"Sally Mae" wrote in message
...
Sorry forgot to mention that I do not know how the code I got from the
macro
differentiate cells but it does work (as long as it works its fine)! So
that
is not a problem. The problem is the content becomes unuseful after the
macro
has been executed due to change of format. I think that the program does
this
because when excel reads the contents of the cells (the conent is pasted
from
another program, otherwise there is no problem) it does not recoginze it
or
something and then stores it as text. However it must be possible to reset
the format as text in the macro. It is very easy to do in excel but i dont
know how to do it in a macro. I does not work to record a macro for this
since no code shows up.....Thanks for any help!!

"Norman Jones" skrev:

Hi Sally,

Thanks Norman but the code just does not work ... and it does
not remove the blanks

It works for me, but perhaps I do not understand your data.

Also it takes a very long time

There are ways to increase speed, but these would be pointless if the
macro
does not funtion!

. I think it is better to use the
code that I got from recording the macro. The code is:

Private Sub findAndRemoveBlanks()
Cells.Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Your original request was to remove blanks from non-alpha cells.

How will your suggested code differentiate types of cell content.

If you wish, you may send me a sample of the data to be converted:

norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )



---
Regards,
Norman