Home |
Search |
Today's Posts |
#1
|
|||
|
|||
moving data
What is the easiest way of moving data from from a1:a5 to a1:e5? I have
hundreds of entries down column A and would like to get them listed in a1:d500. Each entry fills 5 rows in column A and I'd like them to fill the first 5 columns (a to e) in consecutive rows (1-500). -- Gary Beard |
#2
|
|||
|
|||
try this. Then just delete col a
Sub transposeem() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step 5 x = Cells(Rows.Count, 2).End(xlUp).Row + 1 Cells(i, 1).Resize(5, 1).Copy Cells(x, 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True Next i End Sub -- Don Guillett SalesAid Software "gbeard" wrote in message m... What is the easiest way of moving data from from a1:a5 to a1:e5? I have hundreds of entries down column A and would like to get them listed in a1:d500. Each entry fills 5 rows in column A and I'd like them to fill the first 5 columns (a to e) in consecutive rows (1-500). -- Gary Beard |
#3
|
|||
|
|||
Enter this formula in B1.
=OFFSET($A$1,(ROW()-1)*5+COLUMN()-2,0) Drag/copy across to F1 Select B1:F1 Drag/copy down until you get zeros showing up. Select Columns B:F and EditCopy then EditPaste Special(in place)ValuesOKEsc. Delete column A. Alternative is a macro..... Sub ColtoRows_NoError() Dim Rng As Range Dim i As Long Dim j As Long Dim nocols As Integer Application.ScreenUpdating = False Set Rng = Cells(Rows.Count, 1).End(xlUp) j = 1 On Error Resume Next nocols = InputBox("Enter Number of Columns Desired") For i = 1 To Rng.Row Step nocols Cells(j, "A").Resize(1, nocols).Value = _ Application.Transpose(Cells(i, "A").Resize(nocols, 1)) j = j + 1 Next Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents Application.ScreenUpdating = True End Sub In your case, enter 5 in the inputbox when it pops up. Gord Dibben Excel MVP On Wed, 04 May 2005 17:58:08 GMT, "gbeard" wrote: What is the easiest way of moving data from from a1:a5 to a1:e5? I have hundreds of entries down column A and would like to get them listed in a1:d500. Each entry fills 5 rows in column A and I'd like them to fill the first 5 columns (a to e) in consecutive rows (1-500). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding more source data to existing scatter plot | Charts and Charting in Excel | |||
Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
moving data in excel without deleting existing data | Excel Discussion (Misc queries) | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |