remove trailing spaces from over 300,000 rows

[Dundee Lad]

Hello board, I need some help.

I have some simple vba that loops through each cell in column BJ of my worksheet to remove the spaces at the end however this is taking a huge amount of time and I was hoping there was a faster way that someone could show me?

 

[W8253]

Try this:

=TRIM(BJ)

 

[Dundee Lad]

thanks W8253, but need to use VBA. This also removes all extra spaces from a cell, front, middle and end. only want to remove from

 

[vicedo]

Select the column containing the data,

Ctrl+H to find and replace

Find What = hit the space bar once

Replace with = leave blank

 

[Dundee Lad]

Thanks Vicedo, but this also removes all spaces and is not VBA.

 

[vicedo]

I don't know how to write VBA.

type some samples, then i can write you a formula

 

[Dundee Lad]

Thanks vicedo, I know the formula, BUT, I need to use VBA.

 

[FormR]

Hi, you could see if either of these are any faster:

Sub TrimCells1()
With Range("BJ1:BJ" & Range("BJ" & Rows.Count).End(xlUp).Row)
    .Cells = Evaluate(Replace("=IF(RIGHT(@,1)="" "",LEFT(@,LEN(@)-1),@)", "@", .Address))
End With
End Sub

Sub TrimCells2()
Dim v, i As Long
v = Range("BJ1:BJ" & Range("BJ" & Rows.Count).End(xlUp).Row).Value
For i = 1 To UBound(v)
    If Right(v(i, 1), 1) = " " Then v(i, 1) = Left(v(i, 1), Len(v(i, 1)) - 1)
Next i
Range("BJ1").Resize(UBound(v)).Value = v
End Sub

 

[vicedo]

You can try this

Sub test2()
Dim LR As Long, i As Long
LR = Range("BJ" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("BJ" & i)
      [COLOR=#B22222][B]  If Len(.Value) Then [/B][/COLOR].Value = Trim(Right(.Value, Len(.Value) - 1))
    End With
Next i
End Sub

 

[Dundee Lad]

just tried your first one FormR and it works great thank you so much!