How to quickly delete every other row in Excel?
If you want to quickly delete every other row or column in Excel, the main point is how can you quickly select every other row or column first and then apply the delete operation to them. And this article will show you some tricky things about how to select every other row or column first and then delete them quickly.
Microsoft Excel's Filter command can help you quickly filter every other row and then delete them at once, please do as follows:
1. In a blank column besides original data, enter 0,1,0,1…,0,1. In this case, we enter the numbers in helper Column C. See the following screenshot:
2. Select the column C, and click the Filter button under Data tab. Then click the arrow button beside C1, and only check the 0 under the Select All option. See screenshot:
3. Then all rows with 1 are hidden. Select the visible rows with 0, and click the Home > Delete > Delete sheet rows to delete these rows. Then it deletes every other row, and you need to click Filter button again to show only the rows with 1. See screenshots:
4. At last, you can delete the helper column C as you need.
Actually, VBA macro is also a good choice to resolve this problem.
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
VBA code: Delete every other row in selection:
Sub DeleteEveryOtherRow() 'Updateby Extendoffice Dim rng As Range Dim InputRng As Range xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8) Application.ScreenUpdating = False For i = InputRng.Rows.Count To 1 Step -2 Set rng = InputRng.Cells(i, 1) rng.EntireRow.Delete Next Application.ScreenUpdating = True End Sub
3. Press the F5 key to run this macro. Then a dialog pops up for selecting a range. See screenshot:
4. Then click OK, every other row in the selected range from the second row is deleted at once, see screenshot:
For a starter user of Microsoft Excel, using VBA macro is a little complicated. The Select Interval Rows & Columns utility of Kutools for Excel can help you delete every other row quickly.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as follows:
1. Select the range that you want to delete every other rows, and then applying this utility by clicking the Kutools > Select > Select Interval Rows & columns….
2. In Select Interval Rows & Columns dialog box, specify the settings as following screenshot shown, and click OK button to select every other rows first.
3. Then every other row in the selection is selected and highlighted. See screenshot:
4. Then you can quickly delete the selected every other rows by clicking the Home > Delete > Delete sheet rows to delete all of these rows. See screenshots:
With this feature, you can also delete every nth rows or columns as you need.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!