Thread: Transpose every 220 rows horizontally

Hi,

This seems like a quick solution, I have tried modifying the macros of other similar problems individuals have had but my macros coding is not up to par.

In the data below I have a list of data that repeats the same format every 220 rows with data up to column AI, and this goes on for thousands of rows. Rather than individually selecting the range of 220rows and columns and transposing it horizontally, I would like a macros that goes through the list transposing up until the end.

I have attached a sample of the data. In the worksheet "What I Need" it shows what I would like to do with this data.

Thank you,

Haseeb

Try this.

Sub Test()
For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 220
    Range(Cells(N, 1), Cells(N + 219, 35)).Copy Destination:=Sheets(2).Cells(2, Columns.Count).End(xlToLeft).Offset(-1, 1)
Next N
End Sub

The range of cells to be copied has 220 rows. When N = 1 then this expression describes the range 1-220. The next value in the loop is N = 221 (step 220) so the expression then describes rows 221 to 440 etc.