Data uit meerdere rijen in 1 rij achter elkaar plaatsen

Sub hsvineens()
Dim sn, i As Long, j As Long, jj As Long
sn = Sheets(1).Cells(1).CurrentRegion.Offset(1)
ReDim arr(UBound(sn))[COLOR=#FF0000] As String[/COLOR]
    For i = 1 To UBound(sn)
        For j = i To i + 3
           If j + jj < UBound(sn) Then
             arr(i - 1) = arr(i - 1) & Join(Application.Index(sn, j + jj), "|") & "|"
              jj = jj + 10
           End If
        Next j
       jj = 0
    Next i
 With Sheets(2)
  .Cells(1).Resize(UBound(sn)) = Application.Transpose(arr)
  .Columns(1).TextToColumns , , , , , , , , -1, "|"
 End With
End Sub

Sub M_snb()
Dim sn, sp, j As Long, jj As Long
  sn = Sheets(1).Cells(1).CurrentRegion
  sp = Split(Space(10))
  For j = 0 To UBound(sp)
    For jj = j + 2 To UBound(sn) Step 10
      sp(j) = sp(j) & Join(Application.Index(sn, jj), "|") & "|"
    Next
  Next
 With Sheets(2)
   .Cells(1).Resize(UBound(sp)) = Application.Transpose(sp)
   .Columns(1).TextToColumns , , , , , , , , -1, "|"
  End With
End Sub

Sub M_snb()
  sn = Blad1.Cells(1).CurrentRegion.Offset(1)
  
  For j = 1 To 10
    For jj = 0 To 4
      c00 = c00 & Join(Application.Index(sn, j + 10 * jj), "|") & "|"
    Next
    c00 = c00 & vbLf
  Next
  
  With Sheets(2)
     .Cells(1).Resize(10) = Application.Transpose(Split(c00, vbLf))
     .Columns(1).TextToColumns , , , , , , , , -1, "|"
  End With
End Sub