ExcelBanter - transpose 7 rows/7 columns

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - transpose 7 rows/7 columns (https://www.excelbanter.com/excel-programming/351711-transpose-7-rows-7-columns.html)

transpose 7 rows/7 columns

How can I transpose the above other than selecting each record individually
and paste special with transpose. I have thousands of records and would
like to just run a macro to do the job.

thanks!

transpose 7 rows/7 columns

"Annette" wrote in message
...
How can I transpose the above other than selecting each record
individually and paste special with transpose. I have thousands of
records and would like to just run a macro to do the job.

I have a 2-page code macro for transposing a range with
all possible options.
I can send you a text file by e-mail.

Ciao
Bruno

transpose 7 rows/7 columns

Assuming the source data is in Sheet1's A1:G7 (a 7R x 7C grid)

1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
....

and we want to re-lay it vertically into a single column elsewhere, viz:
(I'm guessing this is what you're after)

1
2
3
...
49

In Sheet2,

Put in say, A1:
=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/7),MOD(ROWS($A$1:A1)-1,7))
Copy A1 down to A49

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Annette" wrote in message
...
How can I transpose the above other than selecting each record
individually
and paste special with transpose. I have thousands of records and would
like to just run a macro to do the job.

thanks!

transpose 7 rows/7 columns

Annette wrote:
How can I transpose the above other than selecting each record individually
and paste special with transpose. I have thousands of records and would
like to just run a macro to do the job.

thanks!

I had a spreadsheet that needed to be reformated from rows to columns,
but the order I needed to have it put in was not the order the data was
in. Here is the macro I wrote for this. In the macro I have it
skipping rows in the formating to line up with a template I was using
ex (irow + 10, icol) these can be changed to be in sequence.

Sub FORMAT()
'

Dim irow As Integer
Dim icol As Integer
Dim i As Integer

Selection.Formula = Selection.Value
finalrow = Range("A9999").End(xlUp).Row

'paste the data starting 10 rows from the bottom row
irow = finalrow + 10
icol = 5
For i = 1 To finalrow

'colunm O plus row number the "+ 1" makes it go to the next row
in the loop
'then I tell it where to put the data
Range("O" & i + 1).Copy Destination:=Cells(irow, icol)
Range("A" & i + 1).Copy Destination:=Cells(irow + 1, icol)
Range("M" & i + 1).Copy Destination:=Cells(irow + 2, icol)
Range("N" & i + 1).Copy Destination:=Cells(irow + 3, icol)
Range("H" & i + 1).Copy Destination:=Cells(irow + 10, icol)
Range("E" & i + 1).Copy Destination:=Cells(irow + 11, icol)
Range("K" & i + 1).Copy Destination:=Cells(irow + 13, icol)
Range("Q" & i + 1).Copy Destination:=Cells(irow + 14, icol)
Range("W" & i + 1).Copy Destination:=Cells(irow + 15, icol)
Range("X" & i + 1).Copy Destination:=Cells(irow + 16, icol)
Range("AB" & i + 1).Copy Destination:=Cells(irow + 19, icol)
Range("AC" & i + 1).Copy Destination:=Cells(irow + 20, icol)
Range("AD" & i + 1).Copy Destination:=Cells(irow + 21, icol)
Range("AE" & i + 1).Copy Destination:=Cells(irow + 24, icol)
Range("AF" & i + 1).Copy Destination:=Cells(irow + 25, icol)
Range("AG" & i + 1).Copy Destination:=Cells(irow + 26, icol)
Range("T" & i + 1).Copy Destination:=Cells(irow + 28, icol)
Range("AL" & i + 1).Copy Destination:=Cells(irow + 30, icol)
Range("AM" & i + 1).Copy Destination:=Cells(irow + 31, icol)
Range("AN" & i + 1).Copy Destination:=Cells(irow + 32, icol)

'puts next rows data into a new column and skips a column
icol = icol + 2
Next i
End Sub

transpose 7 rows/7 columns