Hi,

Try this UDF.

Code:
Option Explicit

Function GETNONEMPTYDATA(ByRef Rng As Range, ByVal RowIdx As Long, Optional ByVal ColIdx As Long = 1)
    
    Dim v, e, t(), n As Long
    GETNONEMPTYDATA = CVErr(xlErrRef)
    v = Rng.Columns(ColIdx).Value2
    For Each e In v
        If Len(e) Then
            n = n + 1
            If n = RowIdx Then
                GETNONEMPTYDATA = e
                Exit Function
            End If
        End If
    Next
    
End Function
and use;

in B1 and copied down,

=GETNONEMPTYDATA($A$1:$A$100,ROWS(B$1:B1))