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.
Select every other or nth row, column and then delete them at once:
Kutools for Excel's Select Interval Rows & Columns utilty can help you to find and select every other or nth rows or columns, and then you can do some operations as you need.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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() 'Updateby20160122 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 60 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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoAn easy way to put 0 or 1 in the column instead is to add a formula column: =IF(MOD(ROW(), 2) = 0, 0, 1). That might help if you'd rather not type 0,1,01, etc.
To post as a guest, your comment is unpublished.· 3 years agoanother way is to add a new column in front of your data, so column A is blank, then just enter a b a b a b as you go down the rows in column A - so "a" in A1, "b" in A2, "a" in A3, "b" in A4. then you can drag the new "a" and "b" cells down for all rows of data. then sort column A alphabetically, and you get all the odd rows with an "a" at the top and all the even rows with a "b" at the bottom. this is probably the easiest way.
To post as a guest, your comment is unpublished.· 9 days agoYou're genious :)
To post as a guest, your comment is unpublished.· 1 years agoEasiest? How is that different from the 0,1 method? ;-)
To post as a guest, your comment is unpublished.· 3 years agoThat would be great, except column C is all 0
To post as a guest, your comment is unpublished.· 3 years agoThis is really really helpful. thanks
To post as a guest, your comment is unpublished.· 3 years agoGreat trick...thank you. I love simple sneaky tricks like this