How to Copy Alternate Rows in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Sample Data

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.

Hold the Ctrl Key

Step 2:

  • After selecting the cell, press Ctrl to copy.

Hold the Ctrl Key

Step 3:

  • Then, paste it anywhere you want.

Hold the Ctrl Key

Read More: How to Copy Excluding Hidden Rows in Excel


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.

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

Step 2:

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

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

Step 3:

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

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

Step 4:

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

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

Step 5:

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

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

Step 6:

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

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

Step 7:

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

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

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.
=MOD(ROW(A1),2)=0

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 2:

  • It will give your result as FALSE.

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 3:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 4:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 5:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 6:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 7:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 8:

  • Then, AutoFill the rest cells.

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 9:

  • From the Filter option, choose the Select All

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 10:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 11:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 12:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 13:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Step 14:

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

Use A Formula and the Filter Option to Copy Alternate Rows in Excel

Read More: How to Copy Rows in Excel with Filter


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.

Run A VBA

Step 2:

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

Run A VBA

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

Run A VBA

Step 4:

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

Run A VBA

Step 5:

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

Run A VBA


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo