I was asked by a colleague to transpose a range in blocks of 5 from horizontal to vertical. The data was arranged in 7 columns but he wanted the data split into from tabular data to headings on the vertical and the body text going across the columns. Each block of data was colour coded and this colour scheme was meant to be kept in the output page. It is an interesting problem and I decided to include a union range so I could include the header row (giving meaning to the data) each time a block of 5 rows was copied and transposed to the new sheet.
Here is a view of the raw before data.
Here is a view of what we want the data to look like after the procedure runs.
The following is the code I settled on.
Option Explicit
Sub TransposeIt()
Dim i As Long
Dim iLen As Integer
iLen = 5
With [A1].CurrentRegion
For i = 2 To [A65536].End(xlUp).Row Step iLen
Union(.Rows(1), .Rows(i).Resize(iLen)).Copy
Sheet2.Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True
Next
End With
End Sub
The coding traps the first row, which contains the Excel headings and increments 5 rows each time through the step command. Each loop iterates the rows by 5 so the first iteration will pick up rows 2-6 then rows 7-12 and so on till the bottom of the range is reached. The Union is the intersection between Row1 and the moving rows. The first Union will be rows 1-7 then second will be row 1 and rows 7-12. The following Excel file outlines the procedure and should help to crystallise the procedure.
