How to Copy Every Nth Row in Excel (3 Easy Methods)

We will use the following dataset to highlight the methods to copy every nth row in Excel.

Method 1 – Use OFFSET and ROW Functions

• Let’s copy every 3rd row starting from the 1st row.
• Apply the following formula in cell E5 and drag the Fill Handle icon down. You may need to apply proper formatting to the Date and Sales columns.

• If you want to copy every 4th row starting from the 1st row, change the 3 in the formula to 4.

• Enter the following formula instead if you wish to copy every 3rd row, starting from the 3rd row. Change the 3 in the formula to 4, 5, 6 to copy every 4th, 5th, 6th row, and so on.

Method 2 – Utilize the IF, MOD, and ROW Functions

• Let’s copy every other row starting from the first row. Apply the following formula in cell E5 and drag the Fill Handle icon down.

• Change the 2 in the formula to 3, 4, 5 to copy the 3rd, 4th, 5th row, and so on.

Method 3 – Copy Every Nth Row in Excel VBA

• Go to the Developer tab and select Visual Basic or press Alt + F11 to open the VBA Editor.
• Select Insert and choose Module. This will create a new blank module.

• Copy the following code and paste it onto the blank module.

Sub CopyEveryNthRow()
N = CInt(Application.InputBox("Value of N:", _
"Copy Every Nth Row", , , , , , 1))
ST_Row = CInt(Application.InputBox("Starting row number", _
"Copy Every Nth Row", , , , , , 1))
Set IRange = Application.InputBox(Prompt:="Select range:", _
Title:="Copy Every Nth Row", _
Default:=Selection.Address, Type:=8)
r = 5
Count = CInt(IRange.Rows.CountLarge)
For i = ST_Row To CInt(Count) Step N
IRange.Rows(i).Copy Range("E" & r)
r = r + 1
Next i
End Sub

• Press F5 to run the code. You will be asked for the value of N, the starting row number, and the data range.
• Suppose you want to copy every 3rd (N = 3) row starting from the 1st row (row number = 1) in the sample data range (B5:C16)
• The output result will be as follows.

Read More: Copy Alternate Rows in Excel

How to Copy Every Other Row Using the Fill Handle in Excel

• Let’s copy every other row in range E:F as follows.

• Enter =B5 in cell E5 and drag the Fill Handle icon to the right.

• Excel will show an error in cell F5 as the formatting of cell E5 will also be copied there. Click on the error icon and select Update format.

• Select these two cells along with the adjacent two cells below.
• Drag down the Fill Handle icon from the selection.

• Every other row will be copied as follows.

• Filter out the blank rows to copy the data as shown below.

• If you want to skip the first row, start with the reference from the second row (B6) instead.

• To copy every third row, start with the cell reference from the third row (B7) and drag down two more blank rows with the top row. Increase the number of blanks for every row you want to remove.

Read More: Copy and Paste Thousands of Rows in Excel

Things to Remember

• You need to press Ctrl + Shift + Enter to apply array formulas if you are not using Excel 365.
• Save the file as a macro-enabled workbook to avoid losing VBA code.

Download the Practice Workbook

Related Articles

• Copy Rows in Excel with Filter
• Copy Excluding Hidden Rows in Excel
• Copy Rows Automatically in Excel to Another Sheet
• Copy Rows from One Sheet to Another Based on Criteria

<< Go Back to Copy Rows | Copy Paste in Excel | Learn Excel