loop

[gledister]

imagine in column A we have this situation:

Excel Workbook
	A
1	W103XU13W
2	ROSS
3	98% COTTON / 2% ELASTANE
4	47 FOR ALL
5	32
6	75
7	W103YH17D
8	ROSS
9	98% COTTON / 2% ELASTANE
10	USED MID LIGHT
11	32
12	75
13	W10AE124Z
14	MATT
15	100% COTTON
16	VICENTE WORN
17	64
18	160
Sheet1

what we wanna do is having all these datas in this way:

Excel Workbook
	B	C	D	E	F	G
1	W103XU13W	ROSS	98% COTTON / 2% ELASTANE	47 FOR ALL	32	75
2	W103YH17D	ROSS	98% COTTON / 2% ELASTANE	USED MID LIGHT	32	75
3	W10AE124Z	MATT	100% COTTON	VICENTE WORN	64	160
Sheet2

Any idea for a macro that can resolve this?

Kind regards.

 

[cstimart]

You could use Excel's TRANSPOSE function.

 

[gledister]

Imagine we have a HUGE list in column A....like hundred thousands of them...

 

[gledister]

any idea?

 

[cstimart]

Something like this?

Sub Macro1()
Dim LR As Long
Dim i As Long
Dim j As Long

    LR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    j = 2

For i = 2 To LR Step 7
    Range("B" & j & ":H" & j).Select
    Selection.FormulaArray = "=TRANSPOSE(R" & i & "C[-1]:R" & i + 6 & "C[-1])"
    j = j + 1
Next i
End Sub

 

[gledister]

not working for me...

 

[gledister]

I wanna repeat this action for the whole column:

<TABLE style="WIDTH: 378pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=504><COLGROUP><COL style="WIDTH: 378pt; mso-width-source: userset; mso-width-alt: 18432" width=504><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 378pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=504>Selection.Copy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> ActiveCell.Offset(0, 1).Range("A1").Select</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> False, Transpose:=True</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> ActiveCell.Offset(6, -1).Range("A1:A6").Select</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> Application.CutCopyMode = False</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Selection.Copy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> ActiveCell.Offset(0, 1).Range("A1").Select</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> False, Transpose:=True</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> ActiveCell.Offset(6, -1).Range("A1:A6").Select</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20> Application.CutCopyMode = False

any idea?
</TD></TR></TBODY></TABLE>

 

[cstimart]

Sub Macro1()
Dim LR As Long
Dim i As Long
Dim j As Long

    LR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    j = 1

For i = 1 To LR Step 7
    Range("B" & j & ":H" & j).Select
    Selection.FormulaArray = "=TRANSPOSE(R" & i & "C[-1]:R" & i + 6 & "C[-1])"
    j = j + 1
Next i

    Columns("B:H").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

 

[gledister]

not working but I solved it with the do loop function. thanks anyway.