Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Transpose Every X Number of Rows in Excel with a Macro



I loved your video of 

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

I just used it on a file that I exported out from Quickbooks which i attached.

In the data on sheet 2 I was able to transpose column "M" to get the data output so i can make a table to send the data to my customer. It has the data of the cars. For the price and sales order in Column N and Column O, i am only able to run the Macro to transpose each column one column at a time to get the Price out (column N) and Sales order (column 0) so that i can put it in my table. Is there anyway to highlight and select the data or the car, price and sales order# (column M + N + O) and run the macro in one time instead of three separate time so that i can copy and paste it into the table in one shot? I'm assuming i would need to edit the macro formula module but not sure how to or if it is psosible? 



Did you see my Answer, JC? 
John_Ru (rep: 3992) Jun 16, '22 at 5:50 am
Add to Discussion



Hi JC and welcome to the Forum.

If the data is in adjacent columns, like M N and O in your case, then you could use the modified code from my recent answer Additional question to video "Transpose Every X Number of Rows in Excel with a Macro - Advanced Transpose Technique".

In the first attached revised file (in the more modern .xlsm file type), I've removed duplicated Module2 and added new Module3 including this code (changes in bold):

Sub Transpose_N_Rows_of_X_Columns()
' TeachExcel.com

' Make the macro run faster on large data sets.
Application.ScreenUpdating = False

' Do something with the user-selected cells/range.
xRow = Selection.Rows.Count
xCol = Selection.Column
xWidth = Selection.Columns.Count

' The row that the Transposed data will be put into.
nextRow = 1

' Hard-code Column header example
'Range("C1").Value = "Column Header"

' How many rows to Transpose.
StepValue = InputBox("How many rows of " & xWidth & " columns should be grouped together?")

' Loop through the user-selected data using a step value.
For i = 2 To xRow Step StepValue
        ' Copy the data, using the step value to determine the size of
        ' the copied range.
        Cells(i, xCol).Resize(StepValue, xWidth).Copy       
        'Transpose the data.
        Cells(1, xCol).Offset(nextRow, xWidth).PasteSpecial Paste:=xlPasteAll, Transpose:=True

    ' Increment the nextRow value so the copied data goes onto
    ' a new line.
    nextRow = nextRow + xWidth
Next i

' Remove the "copy lines" from the Transposed data.
Application.CutCopyMode = False
' Make Excel function as expected after the macro is finished.
Application.ScreenUpdating = True

End Sub 

The code will run on any sheet but to demonstate it, I've copied data from Sheet2 columns M, N and O to a new worksheet "Demo sheet" (with a empty first row* since Don's original tutorial assumes a header row). The code is behind the button "Transpose selection by columns". If you click that and answer 1 to the question, the data will be grouped as transposed groups of 3x1.

* Note if you don't have an empty header row before running the macro, the first 1x3 group will be missed since the code statrts on the second row, owing to this line:

For i = 2 To xRow Step StepValue

You could change the 2 to 1 to fix that.

I added a second file as .xls file type in case you have an old version of Excel (please modify your Forum profile to state which version of Excel you use- this can be important for some answers).

Hope this helps.



Did that work for you?
John_Ru (rep: 3992) Jun 15, '22 at 3:10 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login