VBA to trim after last space.

[PCTech]

I have a formula that works
=TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",LEN(A3))),LEN(A3)))

and a macro that works. But not together. Can someone tell me where I need to add quote marks or what it will take to make this work?

Sub SplitLastSpace()
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:A" & Lastrow)
         c.Value = c.Value = Trim(Right(Substitute(A2, " ", Rept(" ", Len(A2))), Len(A2)))
    Next
Application.ScreenUpdating = True
End Sub

I get Compile error: Sub or Function not definded with Rept highlighted.

Thanks,

 

[Norie]

There is no Rept function in VBA, either us Application.Rept or the VBA function String.

 

[Tetra201]

Try

Sub SplitLastSpace()
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:A" & Lastrow)
         c.Value = Split(c.Value, " ")(UBound(Split(c.Value, " ")))
    Next
Application.ScreenUpdating = True
End Sub

 

[Phuoc]

Or try this:

c.Value = Mid(c.Value, InStrRev(Trim(c.Value), " ") + 1)