Selected Answer
Egeptkween
In the attached file, I've copied (and modified) the module from Don's tutorial and made some changes (in bold).
You need to select cells A2:C10189 (you can paste that where the current cell reference is and press "Return") then run the macro in Module1 from VB Project window. State 36 when asked how many rows should be grouped and (after a few seconds) you'll then get "clumps" of 3 rows x36 columns (i.e. the responses for each respondent).
The changes are in bold below (but it's too late here for me to explain fully- essentially I've worked out the number of columns selected, put that in a new variable then adjusted the copy/transpose and row numbers to suit that):
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
Hope this works well for you.
