make it 12 rows from 1 row

[AskExcel]

hi,
i have 1000 rows and each row i need to divide by 12 so that it will become
12 rows.

for example :
from this :
class qty
10 1200
20 1000
....
....
...

to this:
no class qty
1 10 100
2 10 100
3 10 100
4 10 100
5 10 100
6 10 100
7 10 100
8 10 100
9 10 100
10 10 100
11 10 100
12 10 100

 

[carlo]

you can use this function.

i hope i understood correctly what you meant:
'------------------------------------------------------------------------------------
Sub Rows12()

Dim class_ As Variant
Dim qty_ As Double

For i = 2 To 65536
If Cells(i, 1) = "" Then Exit Sub

class_ = Cells(i, 1).Value
qty_ = Round(Cells(i, 2).Value / 12, 2)

Cells(i, 1).Value = class_
Cells(i, 2).Value = qty_

For j = 1 To 11
Rows(i + 1).Insert (xlDown)

Cells(i + 1, 1).Value = class_
Cells(i + 1, 2).Value = qty_
Next j

i = i + 11

Next i

End Sub
'------------------------------------------------------------------------------------

as you see with the for loop i start with 2, if you have titles,
you can leave it like that, if not start with 1.

i rounded the result of the quantity to 2 places after the comma,
you can adjust it like you want, maybe it's best if you don't round
it, and just edit the format of the cells, that way you can continue
calculating with them.

hth

Carlo