see if this helps
This is a discussion on Multiple Columns into one Column within the Excel Questions forums, part of the Question Forums category; Does anyone know how to do this? I have lots of columns that need to be stacked into a single ...
Does anyone know how to do this? I have lots of columns that need to be stacked into a single column (I do not want to combine or concantenate)
eg put all data in column B,column C, column D,column F and column E and stack them one under each other into a single column in column A. I have many many columns each with approx 500 rows of cells.All columns are in the one sheet.
Any ideas appreciated.
Code:Sub MoveAllDataToColumnA() Dim i As Long, ws As Worksheet, rngCopy As Range, rngEnd As Range Set ws = ActiveSheet Do Until ws.Cells(1, 2).Value = "" Set rngCopy = ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp)) Set rngEnd = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0) rngEnd.Resize(rngCopy.Rows.Count, 1).Value = rngCopy.Value rngCopy.EntireColumn.Delete Loop End Sub
"Something is better than nothing"
"Optimisim is the key of success"
I think the OP is looking for the opposite...
B C D E 1 Col 1 Col 2 Col 3 Col 4 2 ABC DEF GHI JKL 3 MON PQR STU VWX 4 YZA BCD EFG GIJ 5 KLM NOP QRS TUV 6 WXY ZAB CDE FGH
A 1 Single Column 2 ABC 3 DEF 4 GHI 5 JKL 6 MON 7 PQR 8 STU 9 VWX 10 YZA 11 BCD 12 EFG 13 GIJ 14 KLM 15 NOP 16 QRS 17 TUV 18 WXY 19 ZAB 20 CDE 21 FGH
Code:Sub TableToColumn() Dim Rng As Range, LR As Long, i As Long LR = Range("B" & Rows.Count).End(xlUp).Row For i = 2 To LR Set Rng = Range("B" & i, "E" & i) 'Change range to suit needs Range("A" & Rows.Count).End(xlUp)(2).Resize(Rng.Count) = Application.WorksheetFunction.Transpose(Rng) Next i End Sub
You can do it with this formula
A B C D E F 1 R1, C1 R1, C2 R1, C3 R1, C4 R1, C1 2 R2, C1 R2, C2 R2, C3 R2, C4 R2, C1 3 R3, C1 R3, C2 R3, C3 R3, C4 R3, C1 4 R4, C1 R4, C2 R4, C3 R4, C4 R4, C1 5 R5, C1 R5, C2 R5, C3 R5, C4 R5, C1 6 R1, C2 7 R2, C2 8 R3, C2 9 R4, C2 10 R5, C2 11 R1, C3 12 R2, C3 13 R3, C3 14 R4, C3 15 R5, C3 16 R1, C4 17 R2, C4 18 R3, C4 19 R4, C4 20 R5, C4
Cell Formula F1 =OFFSET($A$1,
and this formula from www.cpearson.com
Last edited by Yahya; Jan 28th, 2010 at 04:19 AM.
Thanks Sandeep, works perfectly!