Tenta assim:

Sub Extrair_UltimoNome()

Dim NOME() As String
Dim UltimoNome As String

NOME = Split(Range("A2").Value, " ")
UltimoNome = NOME(UBound(NOME))

MsgBox UltimoNome

End Sub

Ou assim (direto, sem colocar o ultimo nome em uma variável exclusiva):

Sub Extrair_UltimoNome()

Dim NOME() As String

NOME = Split(Range("A2").Value, " ")

MsgBox NOME(UBound(NOME))

End Sub

Nossa!

Quer dizer que tudo isso:

NOME = Right(Range("A2").Value, Len(Range("A2").Value) - _
        WorksheetFunction.Search("*", WorksheetFunction.Substitute(Range("A2").Value, " ", "*", _
        Len(Range("A2").Value) - Len(WorksheetFunction.Substitute(Range("A2").Value, " ", "")))))

Virou só isso:

NOME = Split(Range("A2").Value, " ")

Então até tinha visto sobre a função Split e UBound no Google, mas não estou conseguindo captar a ideia e por isso não consegui colocar em pratica.

Obrigado, vou pesquisar mais sobre...

Sim, isso mesmo, resumiu pra caramba.

Resumidamente, o split separa uma string em partes.

Como vc tem um nome de pessoas, que usa espaços para separar, é só o que o split precisa:

Porém, note que o nome é uma matriz, foi declarada assim:
NOME()
não assim:
NOME

Para o nome:
ANA LUCIA GREGORIO ALVES

internamente, a matriz ficou:

NOME(0) = "ANA"
NOME((1) = "LUCIA"
NOME(2) = "GREGORIO"
NOME(3) = "ALVES"

Então, foi só usar o UBound pra pegar o último valor. Como a matriz NOME tem 4 valores, de 0 a 3, o últiomo vai ser o 3, assim,
Ubound(NOME) = 3

E, como vc já viu:
NOME(3) = "ALVES"

Abs

Nossa! Agora entendi!

Eu não conseguia colocar na minha cabeça o porque o 3 retornava a ultima string, e com seu exemplo pude verificar que a contagem começa do 0 e não do 1!

Perfeito, você me ajudou muito.

Então resumindo para ver se entendi direito:

1º Tenho que declarar a variável como uma matriz ()

2º Uso Split nessa matriz para separa-la pelo delimitador "espaço" ==> " "

3º Após o Split a matriz recebe 4 variáveis.

4º O Ubound conta quantas variáveis tem na matriz e retorna a ultima variavel.

Tks

Parfait!