Copy rows 2 by 2 at a time.

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXY
1
2
3
4
5SeriolP1P2P3P4P5P6P7P8P9P10P11P12P13P14P15P16P17P18P19P20P21EMP1P2
61N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X
72N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X
83N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X
94N.XN.XN.XN.XN.X12N.XN.XN.XN.X1N.XN.XN.X12N.X1
105N.XN.XN.XN.XN.XN.XN.XN.X1N.XN.XN.X1234N.XN.XN.X
116N.XN.XN.XN.X1N.X1N.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.X
1271N.XN.X1N.XN.XN.XN.XN.XN.X12N.XN.X1N.XN.XN.XN.X
1381N.XN.XN.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X1N.X
149N.XN.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.XN.XN.XN.XN.X1
1510N.X1N.XN.X1N.XN.XN.XN.X12N.XN.X1N.XN.XN.X1N.X
16111N.XN.X12N.XN.XN.XN.XN.XN.XN.XN.XN.X12N.XN.XN.X
171212N.XN.X12N.XN.XN.XN.XN.XN.X1N.XN.X12N.XN.X
18131N.XN.XN.X1N.XN.X1N.XN.X123N.X1N.X1N.X1
1914N.X1N.XN.XN.X1N.X123N.X123N.X1N.XN.XN.X
20
21
22
23
24
25
26
27
28
29SeriolP1P2P3P4P5P6P7P8P9P10P11P12P13P14P15P16P17P18P19P20P21EMP1P2
301N.X1N.XN.X1N.XN.XN.XN.X1N.XN.XN.XN.XN.XN.XN.XN.XN.X
312N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X
32
332N.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.XN.X
343N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X
35
363N.XN.XN.XN.X12N.XN.XN.XN.XN.XN.XN.XN.X1N.X1N.XN.X
374N.XN.XN.XN.XN.X12N.XN.XN.XN.X1N.XN.XN.X12N.X1
Hoja7


Thank you in advance

Regards,
Kishan
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi Krishnan,

Again, place in A30 and copy across and down as necessary.

Excel Formula: 
=IF(MOD(ROW(),3)=2,"",OFFSET(A6,-2*INT((ROW()-30)/3),0))

I'll leave the VBA version to MUMPS!

Regards
 
Upvote 1
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
 
Upvote 1
Solution
Kishan said:
Now I want to make copies of 12 rows of 6 lines sets using =COMBIN(12,6)
Click to expand...
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)
Click to expand...
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...
 
Upvote 1
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:
  1. 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
  2. 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
docs.google.com

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 docs.google.com
 
Upvote 1
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 
....
 
Upvote 1
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
 
Upvote 1
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.
 
Upvote 1
Try:
VBA Code: 
Sub CopyRows()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 6 To 19
        Cells(Rows.Count, "A").End(xlUp).Offset(2).Resize(2, 22).Value(11) = Range("A" & x).Resize(2, 22).Value(11)
    Next x
    Rows(30).Delete
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Formulas and relative pointing
2
Replies
15
Views
577
atrainwreck
A
C
  • Question
Macro that changes cell after each copy and paste
2
Replies
11
Views
466
PeteWright
PeteWright
C
  • Question
Compare cells in rows from 2 differents sheets before copy
2
Replies
15
Views
413
Tupe77
T
W
  • Solved
Row Transfer Macro
Replies
9
Views
132
mumps
mumps
B
  • Question
Copying Data from Sheet 1 to Sheet 2 and adding rows- Code Needed
2 3 4
Replies
30
Views
933
mumps
mumps

Forum statistics

Threads
1,215,915
Messages
6,127,699
Members
449,398
Latest member
m_a_advisoryforall

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top