Obtener Ultima Celda con valores

Pruebe con:

Public Function ÚltimaCeldaLibroCerrado(sLibro As String, sHoja As String) As Variant
    'Nota1: dependiendo de la versión de Excel, puede ser necesario establecer una referencia a la _
            librería Microsoft ActiveX Data Objects que tenga la versión más alta (desde _
            Herramientas->Referencias)
    'Nota2: se declara como variant lo devuelto por esta función para poder devolver el código y _
            la descripción del error, si se produce.
    Dim sConexión As String
    Dim strSQL As String
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    sConexión = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sLibro & _
        ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
On Error GoTo Captura
    cnn.Open ConnectionString:=sConexión
    strSQL = "SELECT * FROM [" & sHoja & "$]"
    rst.Open Source:=strSQL, ActiveConnection:=cnn, CursorType:=adOpenKeyset, Options:=adCmdText
    rst.MoveLast
    ÚltimaCeldaLibroCerrado = rst.RecordCount + 1
    rst.Close
    cnn.Close
    Exit Function
Captura:
    ÚltimaCeldaLibroCerrado = Err.Number & " - " & Err.Description
End Function

El primer argumento pasado a la función, sLibro, tiene que ser la ruta completa del libro donde se encuentre la hoja cuya última fila se quiere averiguar.

Saludos_