macro to remove spaces at the end of a cell

[Guest]

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.

 

[Bob Phillips]

cLastRow = Cells(Rows.Count,"B").End(xlUp).Row
For i = 1 To cLastRow
Cells(i,"B").Value = Trim(Cells(i,"B").Value
Next i

 

[KL]

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

 

[Ajtb]

Hi
Something like [B1] = trim([A1])
will remove blank spaces from either end of a text string.

Regards

Andrew Bourke

 

[Guest]

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]

..Columns("A:C")

not

..Columns("A3:C")

KL

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