Macro to Transpose 1 col into 3 cols

[jamesberge]

I am trying to transpose 1 column into 3 columns:

Example:
1
2
3
4
5
6
7
8
9
ect...

into

123
456
789
ect...


Here is the code I have:

-----------------------
Dim i As Long
i = 1
Do While i <> 3001
Range("A1:A3").Select
Application.CutCopyMode = False
Selection.Copy
Range(Cells(i, 2), Cells(i, 3)).Select
problem here-->Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks_:=False
, Transpose:=True
Range("A1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

i = i + 1

Loop
---------------------------------------

It keeps hanging up here:
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks_:=False
, Transpose:=True

I have no clue as why it is hanging...any help?

Thanks,

James

 

[Guest]

Range(Cells(i, 2), Cells(i, 3)).Select

should be
Range(Cells(i, 2), Cells(i, 4)).Select

or just use
Cells(i,2)


However, I would do something like
Sub TransposeData()
Dim i as Long, j as Long
j = 1
for i = 1 to 3004 step 3
cells(i,1).Resize(3,1).copy
cells(j,2).PasteSpecial xlAll, Transpose:=True
j = j + 1
Next
Columns(1).ClearContents
end sub

 

[jamesberge]

Thanks Tom!

Sweet Stuff!

I have coded in Access vba before but some of the functions in excelI
have no clue what they are refering to. Any recommendation of sites
that would fill in the gaps?

BTW the next beer is on me!

James

 

[Guest]

Chip Pearson
http://www.cpearson.com/excel.htm
then go to the topics or pages index pages.
That is a good place to start.

John Walkenback (see developer tips)
http://www.j-walk.com/ss/excel

Debra Dalgleish (Pivot Tables, Data Validation, Conditional Formatting,
Advanced and Autofilters, FAQ)
http://www.contextures.com/tiptech.html

Jon Peltier (charts)
http://peltiertech.com/

Ron de Bruin
http://www.rondebruin.nl/tips.htm

David McRitchie
http://www.mvps.org/dmcritchie/excel/excel.htm

are some. Some of them have links.