Rearranging Data from Columns to Rows

[CJ0206]

Hi All,

I am currently looking to use a VBA code for transposing data.

I currently have a sheet set up which has 19 columns of data that i wish to keep in place and then and addition 7 columns which repeat across the sheet. It is these additional columns that i wish to transpose onto a new row while copying the original 19 columns of data.

Originally I had 19 fixed columns and 3 that needed to be transposed, the number of columns to be transposed has now increased to 7, while I think I have figured out the code to allow for this to happen it appears that additional columns are only reflecting the values found in the first set of 7 columns.

Could you please look over the below and advise if I have missed something when changing the VBA. Please see below:

Sub Rearrange_All()
Dim a As Variant, b As Variant
Dim i As Long, j As Long, k As Long, ubA2 As Long, c As Long
 
a = Range("A1").CurrentRegion.Value
ubA2 = UBound(a, 2)
ReDim b(1 To UBound(a) * (ubA2 - 19) / 7, 1 To 26)
For i = 2 To UBound(a)
For j = 20 To ubA2 Step 7
If Len(a(i, j)) Then
k = k + 1
For c = 1 To 26
b(k, c) = a(i, c)
Next c
b(k, 20) = a(i, j): b(k, 21) = a(i, j + 1): b(k, 22) = a(i, j + 2)
End If
Next j
Next i
With Range("A" & Rows.Count).End(xlUp).Offset(7).Resize(, 26)
 
.Offset(1).Resize(k).Value = b
End With
End Sub

If you could please advise it would be a greatly appreciated.