Transpose rows of repeating data into one column

Use a Dict object to obtain the unique combined name list, then extract the names and write them to a newly created worksheet.

Sub Demo()
    Const HEADER_ROWS = 1
    Dim objDic As Object, rngData As Range
    Dim i As Long, j As Long
    Dim sKey, arrData, ar
    Dim wsOut As Worksheet
    
    Set objDic = CreateObject("scripting.dictionary")
    
    ' Load source table into an array
    Set rngData = Sheets("sheet1").Range("A1").CurrentRegion
    arrData = rngData.Value
    
    ' Get the unique lines with Dict object
    For i = LBound(arrData) + HEADER_ROWS To UBound(arrData)
        sKey = ""
        For j = LBound(arrData, 2) To UBound(arrData, 2)
            If Len(arrData(i, j)) > 0 Then sKey = sKey & "|" & arrData(i, j)
        Next j
        objDic(sKey) = Empty
    Next i
    
    ' Extract names
    Dim oCol As New Collection
    For Each sKey In objDic
        ar = Split(Mid(sKey, 2), "|")
        For i = LBound(ar) To UBound(ar)
            oCol.Add ar(i)
        Next
    Next
    
    ' Wite the ouput to worksheet
    Set wsOut = Sheets.Add
    Dim arrOut() As Variant
    ReDim arrOut(1 To oCol.Count, 1 To 1)
    
    For i = 1 To oCol.Count
        arrOut(i, 1) = oCol(i)
    Next
    
    wsOut.Range("A1").Resize(oCol.Count, 1).Value = arrOut
    
    Set objDic = Nothing
    Set wsOut = Nothing
End Sub

You do not need VBA for this. An array formula works fine:

=TOCOL(Range,1,FALSE)

You can read about TOCOL(array, [ignore], [scan_by_column]) function.

Here I am transposing the data to a single column, while ignoring blank cells (it includes everything by default) and scanning by rows (FALSE is the default behavior but I wanted to be explicit).

If what you really want is to get the unique rows transposed/flattened, then:

=TOCOL(UNIQUE(Range),1)

If you must use VBA, then we can create our own function mimicking TOCOL() (although that's absolutely not necessary):

Function TransposePayroll(inputRange As Range)
    Dim result() As Variant, count As Long, i As Long, j As Long
    ReDim result(0 To inputRange.Rows.count * inputRange.Columns.count - 1)
    count = 0
    For i = 1 To inputRange.Rows.count
        For j = 1 To inputRange.Columns.count
            If inputRange.Cells(i, j).Value <> "" Then
                result(count) = inputRange.Cells(i, j).Value
                count = count + 1
            End If
        Next j
    Next i
    ReDim Preserve result(0 To count - 1)
    TransposePayroll = Application.Transpose(result)
End Function

or if you want the unique rows only, same as TOCOL(UNIQUE()):

Function TransposePayroll_Unique(inputRange As Range)
    Dim dict As Object, result() As Variant, rowKey As String
    Dim i As Long, j As Long, count As Long
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = 1 To inputRange.Rows.count
        rowKey = ""
        For j = 1 To inputRange.Columns.count
            rowKey = rowKey & "|" & inputRange.Cells(i, j).Value
        Next j
        If Not dict.Exists(rowKey) Then
            dict.Add rowKey, i
        End If
    Next i
    
    count = 0
    For Each Key In dict.Keys
        i = dict(Key)
        For j = 1 To inputRange.Columns.count
            If inputRange.Cells(i, j).Value <> "" And _
               inputRange.Cells(i, j).Value <> 0 Then
                count = count + 1
            End If
        Next j
    Next
    
    If count = 0 Then
        UniqueRowsFlattened = ""
        Exit Function
    End If
    
    ReDim result(1 To count, 1 To 1)
    count = 1
    For Each Key In dict.Keys
        i = dict(Key)
        For j = 1 To inputRange.Columns.count
            If inputRange.Cells(i, j).Value <> "" And _
               inputRange.Cells(i, j).Value <> 0 Then
                result(count, 1) = inputRange.Cells(i, j).Value
                count = count + 1
            End If
        Next j
    Next
    
    TransposePayroll_Unique = result
End Function

Bonus:

If you simply have the rows and they're not actually repeating, but you want them to be repeated, here's a formula to get that:

=DROP(REDUCE("",SEQUENCE(ROWS(Range)),LAMBDA(acc,r,VSTACK(acc,REPT(FILTER(INDEX(Range,r,0),INDEX(Range,r,0)<>""),SEQUENCE(COUNTA(INDEX(Range,r,0)),,1,0))))),1)

Then you can use any of the proposed functions above (you'd want to use 3 for [ignore] within TOCOL to ignore both empty and error cells).

Since you asked for VBA - this will do the vertical stacking and the repeating of each row in your original data:

Option Explicit

'Take a range `rng` and return a single-column 1-based 2D array of its values, 
'  where each row in `rng` is repeated for the number of values in that row
Function StackUp(rng As Range)
    Dim data, rv, ubr As Long, ubc As Long, r As Long, c As Long
    Dim i As Long, v, n As Long, numVals As Long
    
    data = rng.Value         'read range content to an array
    ubr = UBound(data, 1)
    ubc = UBound(data, 2)
    
    ReDim rv(1 To ubr * ubc * ubc, 1 To 1) 'max size for return array
    i = 0
    For r = 1 To ubr
        numVals = 0
        For c = 1 To UBound(data, 2) 'how many values in this row?
            If Len(data(r, c)) > 0 Then numVals = numVals + 1
        Next c
        For n = 1 To numVals 'repeat this row `numVals` times in the output
            For c = 1 To ubc
                v = data(r, c)
                If Len(v) > 0 Then
                    i = i + 1
                    rv(i, 1) = v
                End If
            Next c
        Next n
    Next r
    StackUp = ResizeFirstDimension(rv, i)
End Function

'Return a resized copy of 1-based 2D array `data`
' `i` = final ubound for first dimension
Function ResizeFirstDimension(data, i As Long)
    Dim r As Long, c As Long, rv, n As Long
    ReDim rv(1 To i, 1 To UBound(data, 2))
    For r = 1 To i
        For c = 1 To UBound(data, 2)
            rv(r, c) = data(r, c)
        Next c
    Next r
    ResizeFirstDimension = rv
End Function

Example: