How to Copy Alternate Rows in Excel (4 Ways)

We’ll use the following sample dataset to copy alternate rows from it.

Method 1 – Hold the Ctrl Key and Select to Copy Alternate Rows in Excel

Steps:

Hold your Ctrl key.
Select every second row. You can choose individual cells or the entire row (by clicking on the row number on the left).

Press Ctrl + C.

Paste the selection anywhere you want.

Method 2 – Apply the Go to Special Option to Copy Alternate Rows in Excel

Steps:

Enter a column next to your data set.
Type ‘X’ in the first row and leave the second row blank.

Use the AutoFill tool to fill in how many cells you need to copy. This is done by selecting the first two cells and dragging down.
Select all the cells in the column.

From the Editing option, select the Find & Select option.
Choose the Go To Special command.

From the box, click on the Blanks option.
Press Enter.

Only the blank cells get selected.

To delete the blank cells, right-click and select Delete.

Click on the Entire row option.
Press the Enter key.

You will get all the alternate rows. You can copy them and paste them anywhere you want.

Method 3 – Use a Formula and the Filter Option to Copy Alternate Rows in Excel

Steps:

Use the following formula in cell D5.

=MOD(ROW(A1),2)=0

It will give your result as FALSE.

Drag the formula down. We get alternating TRUE-FALSE values.

Select the range and click on the Data tab.
Select the Filter option.

Click on the Filter option.
Unselect FALSE.
Press the Enter key.

All rows that don’t contain the value TRUE in that column will be hidden.

Copy the cell value from the formula bar and paste it into the respective cell in the result column.

AutoFill the rest of the cells.

From the Filter option, choose Select All.

Here’s the result.

Choose the FALSE option from the Filter tab.

Select the shown rows and delete them.

From the Filter, choose the Select All option.

Here’s the final result.

Method 4 – Run a VBA Code to Copy Alternate Rows in Excel

Steps:

Select the cells in the range.

Press Alt + F11 to open the VBA window.
Click on the Insert option.
Select Module.

Paste the following VBA code into the module.

Sub CopyAlternateRows()
Dim SelectionRng As Range
Dim alternatedRowRng As Range
Dim RowNumber, i As Long
Set SelectionRng = Selection
RowNumber = SelectionRng.Rows.Count
With SelectionRng
'select row number 1 to range variable
Set alternatedRowRng = .Rows(1)
'apply loop through each rows and add them to range variable
For i = 3 To RowNumber Step 2
Set alternatedRowRng = Union(alternatedRowRng, .Rows(i))
Next i
End With
'select the alternate rows
alternatedRowRng.Select
End Sub