Matriu f x c -> una sola columna a partir de files amb cel·les no buides

 Sub Macro1()

Dim CantFila As Long
Dim CantCol As Long
Dim c As Single, f As Single, m As Single, n As Single
Dim a As Variant

m = 26 'a partir de la fila 26
n = 1

CantFila = Cells(Rows.Count, 2).End(xlUp).Row
CantCol = Cells(2, Columns.Count).End(xlToLeft).Column

For f = 1 To CantFila Step 1
    For c = 1 To CantCol Step 1

    If (Cells(f, c).Value <> "") Then
        a = Cells(f, c).Value
        Cells(m, 1).Value = a
        m = m + 1
    
    Else
        f = f + 1
        c = 1
        a = Cells(f, c).Value
        Cells(m, 1).Value = a
        m = m + 1
      
    End If

    Next c
Next f

End Sub