Skip to main content

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.

Delete every other row in Excel with Filter command

Delete every other row in Excel with VBA code

Delete every other row or column in Excel with Kutools for Excel


arrow blue right bubble Delete every other row in Excel with Filter command

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:

doc-delete-every-other-row5 -2 doc-delete-every-other-row12

4. At last, you can delete the helper column C as you need.


Select every other or nth row, column and then delete them at once

Kutools for Excel's Select Interval Rows & Columns utility can help you to find and select every other or nth rows or columns, and then you can do some operations as you need. Click to download Kutools for Excel!


arrow blue right bubble Delete every other row in Excel with VBA code

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:

doc-delete-every-other-row5 -2 doc-delete-every-other-row12

arrow blue right bubble Delete every other row or column in Excel with Kutools for Excel

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:

doc-delete-every-other-row5 -2 doc-delete-every-other-row12

Click to know more about this Select Interval Rows & Columns feature.

With this feature, you can also delete every nth rows or columns as you need.

Download and free trial Kutools for Excel Now!


arrow blue right bubbleDemo: Delete every other row or column with Kutools for Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

Description


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!
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
An 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.
This comment was minimized by the moderator on the site
another 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.
This comment was minimized by the moderator on the site
You're genious :)
This comment was minimized by the moderator on the site
Easiest? How is that different from the 0,1 method? ;-)
This comment was minimized by the moderator on the site
This 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!
This comment was minimized by the moderator on the site
That would be great, except column C is all 0
This comment was minimized by the moderator on the site
This is really really helpful. thanks
This comment was minimized by the moderator on the site
Great trick...thank you. I love simple sneaky tricks like this
This comment was minimized by the moderator on the site
This was really helpful. thanks,
This comment was minimized by the moderator on the site
In 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.
This comment was minimized by the moderator on the site
Richarddddd, Sneaky! I like that, and it was useful to me today. Thanks.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations