VBA: alphabetical order for surname

[Nelson78]

Hello everybody.

Suppose I have a list in range A5:A9

JOHN SMITH
PETER PAN
ROBIN HOOD
MARK TWAIN
ROD STEWART

I've to put this list in alphabetical order for surname (may be in the cell we can invert name & surname, so JOHN SMITH could become SMITH JOHN).

How could I?

Thank's.

 

[mse330]

Hey Nelson,

Try the below code & see if it does what you need

Sub SortBySurname()
For x = 5 To 9
    Cells(x, 1) = Mid(Cells(x, 1), InStrRev(Cells(x, 1), " ") + 1) & " " & Left(Cells(x, 1), InStrRev(Cells(x, 1), " "))
Next
Range("A5", Range("A" & Rows.Count).End(xlUp)).Sort [A1]
End Sub