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

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

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).