Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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


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


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()
'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:

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

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 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jeff · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    captainmorgan · 3 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Lucille · 10 months ago
      You're genious :)
    • To post as a guest, your comment is unpublished.
      Lasty · 2 years ago
      Easiest? How is that different from the 0,1 method? ;-)
      • To post as a guest, your comment is unpublished.
        Fox East · 1 years ago
        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!
  • To post as a guest, your comment is unpublished.
    Ducky · 3 years ago
    That would be great, except column C is all 0
  • To post as a guest, your comment is unpublished.
    JTB · 4 years ago
    This is really really helpful. thanks
  • To post as a guest, your comment is unpublished.
    AJ · 4 years ago
    Great trick...thank you. I love simple sneaky tricks like this
  • To post as a guest, your comment is unpublished.
    Marian Cody · 4 years ago
    This was really helpful. thanks,
  • To post as a guest, your comment is unpublished.
    Richarddddd · 4 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Liza · 4 years ago
      Richarddddd,

      Sneaky! I like that, and it was useful to me today. Thanks.