Copy rows 2 by 2 at a time.

Kishan · May 15, 2023

Using Excel 2000

Hi,

My data are set in cells A6:V19 total 14 rows. I want to copy 2 rows at a time and paste them from A30 to down after every 2 row need 1 space for next copy….

For example A6:V7 “2 rows” want to copy in A30:V31 then one empty row, next A7:V8 “2 rows” want to copy in A33:V34 and so on….last rows will be the A18:V19

Example sheet attached…..

NX.xls

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

2

3

4

5

Seriol

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

P15

P16

P17

P18

P19

P20

P21

EM

P1

P2

6

1

N.X

1

N.X

1

N.X

1

N.X

7

2

N.X

8

3

N.X

1

2

N.X

1

N.X

1

N.X

9

4

N.X

1

2

N.X

1

N.X

1

2

N.X

1

10

5

N.X

1

N.X

1

2

3

4

N.X

11

6

N.X

1

N.X

1

N.X

1

N.X

12

7

1

N.X

1

N.X

1

2

N.X

1

N.X

13

8

1

N.X

1

N.X

1

N.X

14

9

N.X

1

N.X

1

N.X

1

15

10

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

16

11

1

N.X

1

2

N.X

1

2

N.X

17

12

1

2

N.X

1

2

N.X

1

N.X

1

2

N.X

18

13

1

N.X

1

N.X

1

N.X

1

2

3

N.X

1

N.X

1

N.X

1

19

14

N.X

1

N.X

1

N.X

1

2

3

N.X

1

2

3

N.X

1

N.X

20

21

22

23

24

25

26

27

28

29

Seriol

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

P15

P16

P17

P18

P19

P20

P21

EM

P1

P2

30

1

N.X

1

N.X

1

N.X

1

N.X

31

2

N.X

32

33

2

N.X

34

3

N.X

1

2

N.X

1

N.X

1

N.X

35

36

3

N.X

1

2

N.X

1

N.X

1

N.X

37

4

N.X

1

2

N.X

1

N.X

1

2

N.X

1

Hoja7

Thank you in advance

Regards,
Kishan

Kishan · Jul 10, 2023

kevin9999 said:

Another option?

VBA Code:

Option Explicit
Sub Kishan()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Hoja7")
    ws.Range("A5:V7").Copy ws.Range("A29")
   
    Dim a, b, i As Long, j As Long, k As Long
    a = ws.Range("A7:V19")
    ReDim b(1 To (UBound(a, 1) * 3), 1 To UBound(a, 2))
   
    i = 1: k = 1
    Do While i < UBound(a, 1)
        For j = 1 To UBound(a, 2)
            b(k, j) = a(i, j)
        Next j
        i = i + 1: k = k + 1
        For j = 1 To UBound(a, 2)
            b(k, j) = a(i, j)
        Next j
        k = k + 2
    Loop
    ws.Range("A33").Resize(UBound(b, 1), UBound(b, 2)).Value = b
   
    With ws.Range("A30:V31")
        .Copy
        For i = 33 To 66 Step 3
            ws.Cells(i, 1).PasteSpecial xlPasteFormats
        Next i
        Application.CutCopyMode = False
    End With  
    Application.ScreenUpdating = True
End Sub

Hello kevin9999, hope all is well I need your help. Now I want to make copies of 12 rows of 6 lines sets using =COMBIN(12,6) = means total 924 combinations like copy line1&2&3&4&5&6, 1&2&3&4&5&7, 1&2&3&4&5&8 and so on up to 924 combinations. Please can you help so the code can work with the new layout as showed below?

Kishan Index.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

2

3

4

Serial Numbers

EM

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

5

Serial Numbers

EM

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

6

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

2

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

8

3

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

7

9

4

N.X

1

2

N.X

1

N.X

1

2

3

4

5

6

N.X

1

2

3

4

5

6

7

10

5

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

N.X

1

2

3

4

5

6

11

6

1

N.X

1

2

N.X

1

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

6

12

7

1

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

13

8

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

14

9

N.X

1

N.X

1

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

6

7

8

15

10

1

2

N.X

1

2

N.X

1

2

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

6

16

11

1

2

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

17

12

1

2

N.X

1

2

N.X

1

N.X

1

2

3

N.X

1

2

3

N.X

1

2

3

4

5

6

7

18

19

20

21

22

23

24

Serial Numbers

EM

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

26

2

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

27

3

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

7

28

4

N.X

1

2

N.X

1

N.X

1

2

3

4

5

6

N.X

1

2

3

4

5

6

7

29

5

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

N.X

1

2

3

4

5

6

30

6

1

N.X

1

2

N.X

1

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

6

31

32

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

33

2

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

34

3

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

7

35

4

N.X

1

2

N.X

1

N.X

1

2

3

4

5

6

N.X

1

2

3

4

5

6

7

36

5

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

N.X

1

2

3

4

5

6

37

7

1

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

38

39

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

40

2

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

41

3

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

7

42

4

N.X

1

2

N.X

1

N.X

1

2

3

4

5

6

N.X

1

2

3

4

5

6

7

43

5

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

N.X

1

2

3

4

5

6

44

8

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

Hoja1

Regards,
Kishan

kevin9999 · Jul 10, 2023

Kishan said:
Now I want to make copies of 12 rows of 6 lines sets using =COMBIN(12,6)

This looks like a duplicate of your other thread: Combin copy total 924 sets where you ask the question:

Kishan said:
But now I want to make copies of 12 rows of 6 lines sets using =COMBIN(12,6)

I'm not sure that you should be taking this approach. The other thread has had plenty of views (52 so far) but no responses as yet. This may be because what you're asking is too difficult for most people to solve - me included! If I was able to assist, I would. There are others on this forum more able than me, and if they're struggling with it...

Kishan · Jul 10, 2023

kevin9999 said:
This looks like a duplicate of your other thread: Combin copy total 924 sets where you ask the question:

I'm not sure that you should be taking this approach. The other thread has had plenty of views (52 so far) but no responses as yet. This may be because what you're asking is too difficult for most people to solve - me included! If I was able to assist, I would. There are others on this forum more able than me, and if they're struggling with it...

Hello kevin9999, thank you for your prompt answer, yes it seems to me also challenging what I am asking for but I need it. I will wait if you can solve it.

Good Luck

Kind Regards,
Kishan

bebo021999 · Jul 10, 2023

Hello. I noticed that you have highlighted the original range and result range with a background color for cells containing "N.X" and with green font color for the serial numbers in column A. Is this intended to make it stand out for someone assisting you to easily see the differences, or is it how it is in your actual file?

To keep it simple, I will provide the raw results without formatting. If you wish to apply formatting, you can easily use conditional formatting after running the code (or create conditional formatting directly within the code).

Looking at the history of your topics, I see that your requirements have been quite diverse and varied at different times. Therefore, to help you easily make edits and modifications based on your specific needs, I will guide you through each step using my process.
Here is an explanation of my intention:

For the 12 values in A6:A17, I will use 6 variables from i1 to i6 and loop through these variables to store the combinations in an array called "combi".For example:
- combi(1,1) = 1-2-3-4-5-6
- combi(2,1) = 1-2-3-4-5-7
- combi(3,1) = 1-2-3-4-5-8
- .........
- combi(924,1) = 7-8-9-10-11-12
Next, I will loop through each combination (combi) and refer to the source data to save each record into an array called "results" (res).For example:
- For combi(1,1) = 1-2-3-4-5-6, create res(1,1) to res(1,n) = record 1
- For combi(2,1) = 1-2-3-4-5-7, create res(2,1) to res(2,n) = record 2...
- .........

Similar steps will be followed for the remaining combinations.
Please let me know if there's anything else I can assist you with.

VBA Code:

Option Explicit
Sub test()
Dim rng, combi(1 To 10000, 1 To 1), res(), sp, s
Dim i&, i1&, i2&, i3&, i4&, i5&, i6&, k&, t&, j&
rng = Range("A6:AA17").Value
ReDim res(1 To 100000, 1 To UBound(rng, 2))

'Create combi array: 1-2-3-4-5-6; 1-2-3-4-5-7;... until the 924th
For i1 = 1 To UBound(rng) - 5
    For i2 = i1 + 1 To UBound(rng) - 4
        For i3 = i2 + 1 To UBound(rng) - 3
            For i4 = i3 + 1 To UBound(rng) - 2
                For i5 = i4 + 1 To UBound(rng) - 1
                    For i6 = i5 + 1 To UBound(rng)
                        k = k + 1
                        combi(k, 1) = i1 & "-" & i2 & "-" & i3 & "-" & i4 & "-" & i5 & "-" & i6
                    Next
                Next
            Next
        Next
    Next
Next

'Loop through each num in each combine: 1;2;3;4;5;6 then 1;2;3;4;5;7 then ...
For i = 1 To k ' k=924
    sp = Split(combi(i, 1), "-") ' split 1-2-3-4-5-6 into 1,2,3,4,5,6
    For Each s In sp
        t = t + 1 ' loop through that 6 numbers
        For j = 1 To UBound(rng, 2)
            res(t, j) = rng(s, j)
        Next
    Next
    t = t + 1
Next
If t = 0 Then Exit Sub
Rows("25:100000").Delete
With Range("A25").Resize(t, UBound(res, 2))
    .Value = res
    .HorizontalAlignment = xlCenter
    ' add more action here for this area: conditional formating, formatnumber,...
End With
End Sub

here a gg drive link for your ref

Book1.xlsm

Sheet1 (2) Merge1Begin,Merge2,Merge2,Merge2,Merge2,Merge2,Merge2,Merge2,Merge2,Merge2 Merge1Begin,Merge2,Merge1Begin,Merge2,Merge2,Merge2,Merge2 Merge1Begin,Merge2,Merge2,Merge2,Merge2,Merge1Begin,Merge2,Merge2,Merge2,Merge2 Merge1Begin

docs.google.com

Kishan · Jul 10, 2023

Outstanding! bebo021999, Hats off to you for doing such a magical work I am grateful to you and appreciate your kind help.

bebo021999 said:
Hello. I noticed that you have highlighted the original range and result range with a background color for cells containing "N.X" and with green font color for the serial numbers in column A. Is this intended to make it stand out for someone assisting you to easily see the differences, or is it how it is in your actual file?

It is exactly how my data looks in the range: A6:AA17 these cells have applied conditional formatting.

bebo021999 said:
To keep it simple, I will provide the raw results without formatting. If you wish to apply formatting, you can easily use conditional formatting after running the code (or create conditional formatting directly within the code).

If it can be possible to create conditional formatting directly within the code I will like if you can add it into the code that will be kind of you.

bebo021999 said:
Looking at the history of your topics, I see that your requirements have been quite diverse and varied at different times. Therefore, to help you easily make edits and modifications based on your specific needs, I will guide you through each step using my process.
Here is an explanation of my intention:

For the 12 values in A6:A17, I will use 6 variables from i1 to i6 and loop through these variables to store the combinations in an array called "combi".For example:

combi(1,1) = 1-2-3-4-5-6

combi(2,1) = 1-2-3-4-5-7

combi(3,1) = 1-2-3-4-5-8

.........

combi(924,1) = 7-8-9-10-11-12

Next, I will loop through each combination (combi) and refer to the source data to save each record into an array called "results" (res).For example:

For combi(1,1) = 1-2-3-4-5-6, create res(1,1) to res(1,n) = record 1

For combi(2,1) = 1-2-3-4-5-7, create res(2,1) to res(2,n) = record 2...

.........Similar steps will be followed for the remaining combinations.

Thank you taking a time and explaining a process step by step.

bebo021999 said:
Please let me know if there's anything else I can assist you with.

I am sorry to trouble you yes I need your support this is ended to copy =COMBIN(12,6) =924 combinations. I need to generate following also…how can I modify the code please need your assistance.
=COMBIN(12,2) =66
=COMBIN(12,3) =220
=COMBIN(12,4) =495
=COMBIN(12,5) =792
=COMBIN(12,6) =924…this is done by you which is perfect.
=COMBIN(12,7) =792

I am very happy getting a solution of my problem.

I wish you good luck and cheers to your bright future.

Kind Regards,
Kishan

bebo021999 · Jul 10, 2023

Quick reply for combinations. Will comback for the CF within code.
In 6-combination case, you can see 6 i's variable and six for-loop were used (i1 - i6)

PHP:

For i1 = 1 To UBound(rng) - 5
    For i2 = i1 + 1 To UBound(rng) - 4
        For i3 = i2 + 1 To UBound(rng) - 3
            For i4 = i3 + 1 To UBound(rng) - 2
                For i5 = i4 + 1 To UBound(rng) - 1
                    For i6 = i5 + 1 To UBound(rng)
                        k = k + 1
                        combi(k, 1) = i1 & "-" & i2 & "-" & i3 & "-" & i4 & "-" & i5 & "-" & i6

Similar to 2- combination:

PHP:

For i1 = 1 To UBound(rng) - 1
    For i2 = i1 + 1 To UBound(rng)
                        k = k + 1
                        combi(k, 1) = i1 & "-" & i2 
....

bebo021999 · Jul 10, 2023

Now come back with the CF:
At the end of code, replace with:

VBA Code:

With Range("A25").Resize(t, UBound(res, 2))
    .Value = res
    .HorizontalAlignment = xlCenter

'-------------------------------------------------------------------
'here is the new added
    With .FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="N.X")
        .Interior.ColorIndex = 5
        .StopIfTrue = False
        .Font.ColorIndex = 2
    End With
    .Columns(1).Font.ColorIndex = 5
End With
'-----------------------------------------------------------

End Sub

Kishan · Jul 10, 2023

bebo021999 said:

Quick reply for combinations.
In 6-combination case, you can see 6 i's variable and six for-loop were used (i1 - i6)

PHP:

For i1 = 1 To UBound(rng) - 5
    For i2 = i1 + 1 To UBound(rng) - 4
        For i3 = i2 + 1 To UBound(rng) - 3
            For i4 = i3 + 1 To UBound(rng) - 2
                For i5 = i4 + 1 To UBound(rng) - 1
                    For i6 = i5 + 1 To UBound(rng)
                        k = k + 1
                        combi(k, 1) = i1 & "-" & i2 & "-" & i3 & "-" & i4 & "-" & i5 & "-" & i6

Similar to 2- combination:

PHP:

For i1 = 1 To UBound(rng) - 1
    For i2 = i1 + 1 To UBound(rng)
                        k = k + 1
                        combi(k, 1) = i1 & "-" & i2
....

Hi bebo021999, Super so easy and versatile tried as you guide it worked 100% with all my request

bebo021999 said:
Will comback for the CF within code.

bebo021999,

Kind Regards,
Kishan

Kishan · Jul 10, 2023

bebo021999 said:

Now come back with the CF:
At the end of code, replace with:

VBA Code:

With Range("A25").Resize(t, UBound(res, 2))
    .Value = res
    .HorizontalAlignment = xlCenter

'-------------------------------------------------------------------
'here is the new added
    With .FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="N.X")
        .Interior.ColorIndex = 5
        .StopIfTrue = False
        .Font.ColorIndex = 2
    End With
    .Columns(1).Font.ColorIndex = 5
End With
'-----------------------------------------------------------

End Sub

Unbelievable bebo021999, but after trying I am speechless I tried with each every selection it clean all previous and replace with new combination magical work I like it very much!

I appreciate your time you spent to solve my entire query gratefully and in the fine way.

I wish you good luck and cheers to your bright future.

Kind Regards,
Kishan

bebo021999 · Jul 10, 2023

I'm glad to hear that it's working well. Please don't hesitate to come back to MrExcel if you have any questions or need assistance.

Copy rows 2 by 2 at a time.

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

kevin9999

Well-known Member

Kishan

Well-known Member

bebo021999

Well-known Member

Book1.xlsm

Kishan

Well-known Member

bebo021999

Well-known Member

bebo021999

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

bebo021999

Well-known Member

Similar threads

Forum statistics

Share this page

Copy rows 2 by 2 at a time.

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock