Macro to trim trailing Spaces in Col A & B

[howard]

I have written code to remove/trim the trailing spaces in Col A & B on sheet 2

Eg BR1 Shirt Sales

in this eg remove space/s only after sales


The macro trims the data in Col A , but not in Col B




It would be appreciated if someone could kindly assist me

 Sub TrimtrailingSpaces()
 Sheets(2).Select
 
FirstRow = 1 
LastRow = Cells(Rows.Count, 1).Resize(, 2).End(xlUp).Row
For Each cell In Range(Cells(FirstRow, 1), Cells(LastRow, 1))
cell.Value = Trim(cell.Value)
Next cell

End Sub

 

[steve the fish]

Your range is only looking in column 1:

Range(Cells(FirstRow, 1), Cells(LastRow, [COLOR=#ff0000]1[/COLOR]))

so change the red 1 to 2 ie column 1 to column 2.

This will be faster if you want to use it:

Dim x As Variant, lr As Long

lr = Application.Max(Range("A" & Rows.Count).End(xlUp).Row, Range("B" & Rows.Count).End(xlUp).Row)
 
Set x = Range("A1:B" & lr)
x = Evaluate("=IF(ROW(1:" & lr & "),TRIM(" & x.Address & "))")
Range("A1:B" & lr) = x

 

[Joe4]

If you want it to do both columns A and B, you need to update the column reference in your range reference to do column B (or "2"), i.e.

For Each cell In Range(Cells(FirstRow, 1), Cells(LastRow, [COLOR=#ff0000][B]2[/B][/COLOR]))

or

For Each cell In Range(Cells(FirstRow, [COLOR=#ff0000][B]"A"[/B][/COLOR]), Cells(LastRow, [COLOR=#ff0000][B]"B"[/B][/COLOR]))

 

[howard]

Thanks for the help Guys

 

[Rick Rothstein]

Your range is only looking in column 1:

Range(Cells(FirstRow, 1), Cells(LastRow, [COLOR=#ff0000]1[/COLOR]))

so change the red 1 to 2 ie column 1 to column 2.

This will be faster if you want to use it:

Dim x As Variant, lr As Long

lr = Application.Max(Range("A" & Rows.Count).End(xlUp).Row, Range("B" & Rows.Count).End(xlUp).Row)
 
Set x = Range("A1:B" & lr)
x = Evaluate("=IF(ROW(1:" & lr & "),TRIM(" & x.Address & "))")
Range("A1:B" & lr) = x

The TRIM function will reduce multiple consecutive internal spaces to single spaces and will delete leading spaces. If these are possibilities for the OP's data and if the OP truly wants to delete only the trailing spaces (which his thread title suggests), then he would need something along these lines instead...

[table="width: 500"]
[tr]
	[td]Sub TrimTrailingSpaces()
  Dim Addr As String
  Addr = "A1:B" & Columns("A:B").Find("*", , xlValues, , xlRows, xlPrevious).Row
  Range(Addr) = Evaluate(Replace("IF(@="""","""",LEFT(@,FIND(""|"",SUBSTITUTE(@,RIGHT(TRIM(@)),""|"",LEN(@)-LEN(SUBSTITUTE(@,RIGHT(TRIM(@)),""""))))))", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[howard]

Thanks Rick. That's exactly what I require

 

[Akuini]

If you want to delete trailing spaces you can use RTrim function.

 

[howard]

Thanks for your input