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.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 years 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.· 6 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.· 3 years agoYou're genious :)
- To post as a guest, your comment is unpublished.· 4 years agoEasiest? How is that different from the 0,1 method? ;-)
- To post as a guest, your comment is unpublished.· 4 years agoThis is actually very useful if you have a large number of rows and can't enter the 2 settings in manually. if you drag 0,1 down, it continues to 2, 3, 4...but if you drag a,b down, it will repeat as a,b,a,b...all the way down. Thanks!
- To post as a guest, your comment is unpublished.· 6 years agoThat would be great, except column C is all 0
- To post as a guest, your comment is unpublished.· 6 years agoThis is really really helpful. thanks
- To post as a guest, your comment is unpublished.· 6 years agoGreat trick...thank you. I love simple sneaky tricks like this
- To post as a guest, your comment is unpublished.· 6 years agoThis was really helpful. thanks,
- To post as a guest, your comment is unpublished.· 7 years agoIn cell B1: =row(A1)
Drag this down all of column B.
In cell C1: =mod(B1,2)
Drag this down all of column C.
Apply a filter and in column C select all 0s or 1s as required.
- To post as a guest, your comment is unpublished.· 7 years agoRicharddddd,
Sneaky! I like that, and it was useful to me today. Thanks.