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

Llewellyn

New Member
Joined
Apr 17, 2019
Messages
2
Hi,

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

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.


<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>

</tbody>

<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>
</tbody>
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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.
 
Last edited:

Llewellyn

New Member
Joined
Apr 17, 2019
Messages
2
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results starting "C1".
Code:
[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
 

Forum statistics

Threads
1,081,416
Messages
5,358,544
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top