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.

Download Practice Workbook

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

4 Suitable Ways to Copy Alternate Rows in Excel

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 
• Select the alternate rows.

Step 2:

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

Step 3:

• Then, paste it anywhere you want.

Read More: How to Copy Data from One Cell to Another in Excel Automatically

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
• Then, choose the Go To Special

Step 4:

• From the box, click on the Blanks
• 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 the Delete

Step 7:

• Click on the Entire row
• Then, press the Enter

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

Read More: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)

Similar Readings

• Excel VBA to Copy Rows to Another Worksheet Based on Criteria
• Use VBA to Paste Values Only with No Formatting in Excel
• How to Autofilter and Copy Visible Rows with Excel VBA
• Copy Unique Values to Another Worksheet in Excel (5 Methods)
• How to Copy Merged and Filtered Cells in Excel (4 Methods)

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

Step 5:

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

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

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

Step 14:

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

Read More: How to Copy Rows in Excel with Filter (6 Fast Methods)

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
• Select the Module

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: How to Copy Multiple Rows in Excel Using Macro (4 Examples)

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.

The Exceldemy staff will respond to your inquiries as quickly as possible.

Stay with us and keep learning.

Related Articles

• Excel VBA: Copy Cell Value and Paste to Another Cell
• VBA Code to Compare Two Excel Sheets and Copy Differences
• Excel VBA: Copy Range to Another Workbook
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• How to Paste From Clipboard to Excel Using VBA
• Disable Copy and Paste in Excel without Macros (With 2 Criteria)
• How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)