Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose several columns to one long column
I have 999 columns in a worksheet, each with 8 rows. The data in the first
column, A1-A8 are data points 1-8. Then, the data in column B1-B8 are data points 9-16..etc. I can't find a command to transpose all 999x8rows of data into one extremely long column with the data points in the order they were taken, 1, 2, 3, ...998, 999. Any insight? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose several columns to one long column
You have a typo or I am mis-understanding.
8 rows of number 1-999 would be 999/8 = 125 columns Whatever the case this macro from Bernie Dietrick will give you one column. If you really have 999x8 rows columns. it will take a while. Sub OneColumnV2() '''''''''''''''''''''''''''''''''''''''''' 'Macro to copy columns of variable length' 'into 1 continous column in a new sheet ' 'Modified 17 FEb 2006 by Bernie Dietrick '''''''''''''''''''''''''''''''''''''''''' Dim iLastcol As Long Dim iLastRow As Long Dim jLastrow As Long Dim ColNdx As Long Dim ws As Worksheet Dim myRng As Range Dim ExcludeBlanks As Boolean Dim myCell As Range ExcludeBlanks = (MsgBox("Exclude Blanks", vbYesNo) = vbYes) Set ws = ActiveSheet iLastcol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column On Error Resume Next Application.DisplayAlerts = False Worksheets("Alldata").Delete Application.DisplayAlerts = True Sheets.Add.Name = "Alldata" For ColNdx = 1 To iLastcol iLastRow = ws.Cells(ws.Rows.Count, ColNdx).End(xlUp).Row Set myRng = ws.Range(ws.Cells(1, ColNdx), _ ws.Cells(iLastRow, ColNdx)) If ExcludeBlanks Then For Each myCell In myRng If myCell.Value < "" Then jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _ .End(xlUp).Row myCell.Copy Sheets("Alldata").Cells(jLastrow + 1, 1) _ .PasteSpecial xlPasteValues End If Next myCell Else myRng.Copy jLastrow = Sheets("Alldata").Cells(Rows.Count, 1) _ .End(xlUp).Row myCell.Copy Sheets("Alldata").Cells(jLastrow + 1, 1) _ .PasteSpecial xlPasteValues End If Next Sheets("Alldata").Rows("1:1").EntireRow.Delete ws.Activate End Sub Gord Dibben MS Excel MVP On Mon, 4 Feb 2008 13:37:04 -0800, sofast1651 wrote: I have 999 columns in a worksheet, each with 8 rows. The data in the first column, A1-A8 are data points 1-8. Then, the data in column B1-B8 are data points 9-16..etc. I can't find a command to transpose all 999x8rows of data into one extremely long column with the data points in the order they were taken, 1, 2, 3, ...998, 999. Any insight? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
How to transpose a long list of text across cells | Excel Discussion (Misc queries) | |||
Multiple columns of data into one long column | Excel Discussion (Misc queries) | |||
Is it possible to transpose 2 columns of data into 1 row? | Excel Discussion (Misc queries) |