How to Copy Alternate Rows in Excel (4 Ways)

While dealing with huge data analysis, you may need to copy multiple alternate rows and paste them someplace at the same time. Excel does not have a built-in function or formula to accomplish this. As a result, in this article, we will demonstrate how to copy alternate rows in Excel.

How to Copy Alternate Rows in Excel: 4 Suitable Ways

In the sections below, we’ll copy alternate rows in Excel using a combination of Excel settings and formulas. Later on, we’ll show you how to perform the same thing using VBA. An example data set is supplied in the image below, which will be utilized to complete the procedure.

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

If your data isn’t too large, you may select it with the Ctrl button on your keyboard and the mouse. To do so, follow the instructions below.

Step 1:

• Hold your Ctrl  key.
• Select the alternate rows.

Step 2:

• After selecting the cell, press Ctrl +  C  to copy.

Step 3:

• Then, paste it anywhere you want.

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

You may copy alternate rows in Excel by using the Editing option. Follow the instructions below to do so.

Step 1:

• Enter a column beside your data set.
• Type ‘X’ in the first row and leave the second-row blank as shown in the below image.

Step 2:

• Then, use the AutoFill tool to fill in how many cells you need to copy.
• Select the cells.

Step 3:

• From the Editing option, select the Find & Select option.
• Then, choose the Go To Special command.

Step 4:

• From the box, click on the Blanks option.
• Then, press Enter.

Step 5:

• You will get your blank cells selected, now you will delete all your blank cells.

Step 6:

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

Step 7:

• Click on the Entire row option.
• Then, press the Enter key.

Therefore, you will find all your alternate rows in a serial. Now, you can copy them and paste them anywhere you want.

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

You may copy alternate rows by using the formula in combination with the Filter option in Excel. Follow the procedures outlined below to complete the task.

Step 1:

• Type the following formula in cell D5.

Step 2:

• It will give your result as FALSE.

Step 3:

• Drag them down to as many cells as you want. It will give results with ‘FALSE’ and ‘TRUE’ in alternative rows.

Step 4:

• After selecting the range, click on the Data tab menu.
• Select the Filter option.

Step 5:

• Then, click on the Filter option.
• Unselect the ‘FALSE’
• Press the Enter key.

Step 6:

• As a result, you will get the value only for the ‘TRUE’.

Step 7:

• Copy the cell value from the formula bar and paste it into the ‘TRUE’

Step 8:

• Then, AutoFill the rest cells.

Step 9:

• From the Filter option, choose the Select All

Step 10:

• It will result as you see in the below image. So, we need to remove the ‘FALSE’ value in cells.

Step 11:

• Choose the ‘FALSE’ option from the Filter tab.

Step 12:

• The cell’s value which is valued ‘FALSE’ will appear.
• Just, delete them.

Step 13:

• Again, from the Filter option, choose the Select All option.

Step 14:

• Finally, you can obtain the results in an alternative manner.

Read More: Copy Excluding Hidden Rows in Excel

4. Run a VBA Code to Copy Alternate Rows in Excel

Additionally, with the help of the VBA code, you can also copy alternate rows. To do so. Follow the steps below.

Step 1:

• Select the cells in the range.

Step 2:

• Press  Alt  +  F11   to open the VBA Macro.
• Click on the Insert option.
• Select the Module from there.

Step 3:

• Paste the following VBA code.

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

Step 4:

• Save the program and press F5 to run it. You will get the alternate rows will be selected.

Step 5:

• Now, press Ctrl  + C  to copy and paste it where you desire.

Read More: Copy and Paste Thousands of Rows in Excel

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

To summarize, I hope you now understand how to copy alternate rows in Excel using formulas and VBA. All of these techniques should be taught to and applied by your data. Look over the practice book and put what you’ve learned to use. Because of your invaluable support, we are inspired to continue offering seminars like these. If you have any questions, please do not hesitate to contact us. Please let us know what you think in the comments section below.

Related Articles

• Copy Every Nth Row in Excel
• Copy Rows in Excel with Filter
• 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