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.
- Hold your Ctrl
- Select the alternate rows.
- After selecting the cell, press Ctrl + C to copy.
- 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.
- 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.
- Then, use the AutoFill tool to fill in how many cells you need to copy.
- Select the cells.
- From the Editing option, select the Find & Select
- Then, choose the Go To Special
- From the box, click on the Blanks
- Then, press Enter.
- You will get your blank cells selected, now you will delete all your blank cells.
- To delete the blank cells, right-click the mouse and select the Delete
- 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.
- 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.
- Type the following formula in cell D5.
- It will give your result as FALSE.
- Drag them down to as many cells as you want. It will give results with ‘FALSE’ and ‘TRUE’ in alternative rows.
- After selecting the range, click on the Data tab menu.
- Select the Filter
- Then, click on the Filter
- Unselect the ‘FALSE’
- Press the Enter
- As a result, you will get the value only for the ‘TRUE’.
- Copy the cell value from the formula bar and paste it into the ‘TRUE’
- Then, AutoFill the rest cells.
- From the Filter option, choose the Select All
- It will result as you see in the below image. So, we need to remove the ‘FALSE’ value in cells.
- Choose the ‘FALSE’ option from the Filter
- The cell’s value which is valued ‘FALSE’ will appear.
- Just, delete them.
- Again, from the Filter option, choose the Select All
- Finally, you can obtain the results in an alternative manner.
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.
- Select the cells in the range.
- Press Alt + F11 to open the VBA Macro.
- Click on the Insert
- Select the Module
- 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
- Save the program and press F5 to run it. You will get the alternate rows will be selected.
- Now, press Ctrl + C to copy and paste it where you desire.
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.
- 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)