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.


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.

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

Hold the Ctrl Key

Step 2:

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

Hold the Ctrl Key

Step 3:

  • Then, paste it anywhere you want.

Hold the Ctrl Key

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.

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

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

Step 4:

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

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

Step 7:

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

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.

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

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


Similar Readings


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

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

Step 5:

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

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

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

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 (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.

Run A VBA

Step 2:

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

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

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

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