thank you both very much.. I know realize its columns A B and C .. so i could
do this couldln't I?

Sub test2()
Dim rng As Range
With ActiveSheet
Set rng = Intersect(.UsedRange, .Columns("A3:C"))
If Not rng Is Nothing Then _
rng.Replace " ", ""
End With
End Sub


"KL" wrote:

you can use the function TRIM like this:

=TRIM(B1)

or the folllowing macros:

this if need to remove spaces at the beginning and the end as well as
duplicate ones in the middle of the string:

Sub test()
Dim LastRow As Long, i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
For i = 1 To LastRow
.Cells(i, 2) = WorksheetFunction.Trim(.Cells(i, 2))
Next i
End With
End Sub

or this if all spaces need to be removed:

Sub test2()
Dim rng As Range
With ActiveSheet
Set rng = Intersect(.UsedRange, .Columns("B"))
If Not rng Is Nothing Then _
rng.Replace " ", ""
End With
End Sub

Regards,
KL

"Michael A" wrote in message
...
ok, here is a tough one i think. I have a file that i copy into excel
every
day. while all the information is the same, in the B column there is a
random
amount of spaces included by the program that gives me the data. Thanks to
Dave i now have a macro that I can use to compare two sheets for
duplicates,
problem is that its not finding the duplicates due to the different number
of
spaces.. anyone know how to make a macro that removes all the spaces? the
values in the B columns are all different lengths.