Cts, boa tarde.
Não estou com um computador para testar no momento. Mas vamos lá.

Como os nomes podem variar de tamanho e de quantidade(nomes compostos e quantidades de sobrenome diferentes), a melhor forma de obter o último sobrenome seria:

sub exemplo()
x = split(range("D2").value," ")
msgbox x(ubound(x))
end sub

Poste como voce montou a sua rotina, porque a que o colega Farah passou funciona perfeitamente com um nome digitado em D2, e com a mensagem de erro que voce citou da a entender que está montando um Array, assim afica mais fácil dar uma sugestão precisa.

[]s
Mauro Coutinho

Boa tarde,

Preciso o nome da pessoa menos o Sobrenome

Carlos Almeida de Carvalho - preciso pegar Carlos Almeida de

Debora dos Santos Pereira - preciso pega Debora dos Santos

Dim MyResult() As String

MyText = "Carlos Almeida de Carvalho"
MyResult = Split(MyText, " ")

MsgBox MyResult(UBound(MyResult))

Fiz o código acima, ele tira o Sobrenome Carvalho, mas não estou sabendo como mostrar o nome Carlos Almeida de

Se alguém pude me ajudar agradeço.

Procure se manter o mesmo tópico ate ser concluido.
Experimente:

Sub separa()
Dim MyResult() As String, MyText As String
Dim Sobrenome As String, Nome As String
Dim x As Integer
MyText = "Carlos Almeida de Carvalho"
MyResult = Split(MyText, " ")
For x = 0 To UBound(MyResult) - 1
    If x = 0 Then
        Nome = MyResult(x)
    Else
        Nome = Nome & " " & MyResult(x)
    End If
Next
Sobrenome = MyResult(UBound(MyResult))
MsgBox Sobrenome & " _/_  " & Nome
End Sub