Transpose Every X Number of Rows in Excel with a Macro - Advanced Transpose Technique

How to Transpose groups of rows from a large column of data; this allows you to do things like take every 3 rows and transpose them into a single row to the right of the current data set.

This tutorial includes these sections:
- Explanation of how to perform the Advanced Transposing method.
- How to transpose any number of rows from a single column into a nice and neat data-set.
- How to customize and edit the Advanced Transposing method to work for your data set and your needs.
- How to combine transposed data into a single cell for each transposed row.
- and even more :)

I hope you guys enjoy the tutorial! :)

[tgiglio]

Hi there! Great video! You made it quite simple to understand! Thanks. Question: what if the 'X Number' of rows to copy and transpose varies and is conditional? In other words, instead of copying at every 3rd or 5th row, you need to copy and transpose rows, until you hit a row that says in the cell "go to this site" which repeats going down the data set in different places?

[ZenGardenOzone]

Sub Transpose_N_Rows()

Application.ScreenUpdating = False

xRow = Selection.Rows.Count
XCol = Selection.Column

nextRow = 1

stepValue = InputBox("How many rows should be grouped together?")

For i = 1 To xRow Step stepValue

Cells(i, XCol).Resize(stepValue).Copy
Cells(1, XCol).Offset(nextRow, 3).PasteSpecial Paste:=x1PasteAll, Transpose:=True

nextRow = nextRow + 1

Next

Application.ScreenUpdating = True

End Sub



I'm inputting this and I'm getting an error

Run-time error '1004':
PasteSpecial method of Range class failed

[AshishRanjan-irpp]

can anyone help me with VBA code written here ??
unable to download the file and code is not completely visible else i would have written it

[constanzavega]

Hello, so how would you do the exact opposite? from the second table to the first long list?

[josephfianko]

Really liked the tutorial. Will like to know to apply this to multiple columns without having to select it one after the other to have it transposed.

[ziggystardust]

Excellent!! Was breaking my brain trying to figure this out -- great solution!

[akshayvernekar]

Can we do vice versa like transpose columns to X number of rows

[muwanguzitimothy]

this seems to be the best solution for my issue but i have failed to get the downloadable file for this very lesson. Anyone to help me🙏🙏🙏🙏🙏🙏

[maxweels]

How to do this with Google Sheet or Numbers for Mac?

[cflorenzo]

i added your code, didnt not work, maybe its not complete, but i would love to see all the code you have in this video

[muhammedyilmaz]

thanks. It solved my problem. Great job

[mohdtanzeem]

how to remove white space along with this code you have written in your video help me soon

[wayneedmondson]

Great tutorial.. love the explanations of the logic in VBA. Good teaching and learning! Thumbs up!
PS - By the way here is a formula I wrote to unstack the data in your Example 2, starting in cell D1: =OFFSET($A$1, (COLUMNS($D$1:D1)-1+(ROWS($D$1:D1)-1)*5), 0). If you write this formula in D1 and copy it down and right to column H, it will do the same as your macro. If you have data items 6, 7, 8, etc. then modify the "5" at the end of the formula to adjust for more columns. That constant could be made to be dynamic in several ways, but most of the time when unstacking data, you know how many columns you need and so just as easy to hard code it into the formula. This is just another way to solve the problem.. I totally dig the VBA method too and Power Query could also do it. I like to try to solve things with formulas first, as some clients are paranoid and don't allow macro enabled workbooks onto their networks and PQ requires the extra step of refreshing. Each method has its own merits and appropriate use profiles. Thanks again for the inspiration to work on this problem!

[NikhilSharma-onbg]

Your video is very useful but I have an query you had run macro in 5 coloum but if I have different different type data like 2, 3, 4 any other when what we do?

[clintgillespie]

Thank you! I learned how to do this in college and could not remember how to program in VBA for the life of me. I really needed to do this again and your video helped so much.

[max]

Hi Sir,
In the above transpose data script, could you please change the stepvalue to specific text instead of a number with wildcard operator so that row range should transpose the data to that specific text value it should then move to next row one that text value recognized....pls give the code

[dominicconradie]

Hi thank you for this video really amazing, I’m trying to do exactly the opposite of what you have done. So my info is in a row and I want to put it in a column. Name and four dates. I need the name to appear next to each date and when I transpose I only get every fourth row so I guess it’s got to do with the loop.

[gbfht]

Can you do this for three columns of data? Transpose won't work because I have too many rows.

[baseraadvertising]

Seriously I was searching this since yesterday now I got it... Thanks a lot... :)

[imyh-m]

Great video, how do i access/download the macro used for this? I can't find it on the website- is it something i need to pay for?