Macro to Transpose contiguous rows in the next column in excel 2003

[jquintana78]

I have the next table:

[pre]

A	B	C	D	        E	F	G	H
2	Order	Qty	Description	Desc1	Desc2	Desc3	Cost
3	456	25	Gloves, rubber				1.91
4			Mfg: McMaster Carr
5			Sch B: 3920.10.0000

[/pre]
I need a macro to transpose rows D3,D4,D5 to E3, E4, E5 respectively.


My real data are thousand of lines, and have a empty row between each set of three lines.


I will really appreciate your help with this issue.


jquintana78

 

[Luke M]

I'm assuming you actually want it in E3:G3, no E3:E5?

[pre]

Sub TranposeStuff()
Dim i As Long
i = 3
Application.ScreenUpdating = False
Do While Cells(i, "D").Value <> ""
Range(Cells(i, "D"), Cells(i + 2, "D")).Copy
Cells(i, "E").PasteSpecial Paste:=xlPasteValues, Transpose:=True
i = i + 4
Loop

Range(Cells(3, "D"), Cells(i, "D")).ClearContents

'if you want to remove all the extra blank lines, uncomment the following
'Range(Cells(3, "E"), Cells(i, "E")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Application.ScreenUpdating = True
End Sub

[/pre]

 

[jquintana78]

Thanks a lot Luke M

You are right regarding to the cell position I needed, I had a very hard day and copy the wrong sample.


I will test