duplicate an x amount of rows but with each duplication increment by 1 all the numerical values within the row

[Llewellyn]

Hi,

I have the following problem and not sure how to go about doing this.
I have the axample below"

<colgroup><col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> </colgroup><tbody>
[TD="width: 102"]

<colgroup><col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> </colgroup><tbody>

[TD="class: xl70"]I need to duplicate the 4 rows and 2 columns, 4000 times but with every duplication the value of "10" in both cells need to increment by 1 as well. I hope someone can help me with this one.

[/TD]

</tbody>

[/TD]

[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl67, align: right"][/TD]
[TD="class: xl66"][/TD]

</tbody>

 

[Special-K99]

So you have this

10 10
10 10
10 10
10 10

and you want this

10 10
10 10
10 10
10 10
11 11
11 11
11 11
11 11
12 12
12 12
12 12
12 12

continuing until the value is 4009 (10-4009 is 4000 times, 10-4000 is 4001 times).

if this is a one off it would be quicker to

1. put the value 10 in A1
2. Select bottom right corner of A1 until it turns to a cross, Hold down ctrl until you see a little cross appear, drag down until row 4009.
3. Press ctrl-A to copy column A
4. press Home, press Ctrl-down arrow, move down one row press Ctrl-V, Ctrl-down arrow, move down one row press Ctrl-V, Ctrl-down arrow, move down one row press Ctrl-V

Now you have the numbers 10-4009 covering 16000 rows in column A

5. Select column A, Data Sort OK
6. Copy column A to column B.

 

[Llewellyn]

Thank you for the reply.
Unfortunately the info that I tried to paste did not came out correct, but I will try another method.

I have this in two columns

10 10A
10 10B
10 10C
10 10A1
10 10A2
10 10A3
10 10A4

Lets assume top left is cell A1 and bottom right is B7.

I need to duplicate the block between A1 and B7 1600 times , but increment only the value of 10 between the two two columns by 1

So it should look like this

10 10A
10 10B
10 10C
10 10A1
10 10A2
10 10A3
10 10A4
11 11A
11 11B
11 11C
11 11A1
11 11A2
11 11A3
11 11A4


Thanks

 

[MickG]

Try this for results starting "C1".

[COLOR="Navy"]Sub[/COLOR] MG19Apr35
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, txt
Ray = Range("A1:B7")
Num = Ray(1, 1)
ReDim nray(1 To UBound(Ray, 1) * 1600, 1 To 2)
[COLOR="Navy"]For[/COLOR] n = 1 To 1600
   [COLOR="Navy"]For[/COLOR] nn = 1 To UBound(Ray, 1)
        c = c + 1
        nray(c, 1) = Num
        nray(c, 2) = Num & Mid(Ray(nn, 2), 3, Len(Ray(nn, 2)) - 2)
   [COLOR="Navy"]Next[/COLOR] nn
Num = Num + 1
[COLOR="Navy"]Next[/COLOR] n
Range("C1").Resize(UBound(Ray, 1) * 1600, 2).Value = nray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick