How do I delete alternating rows in excel

Discussion in 'Microsoft Excel Worksheet Functions' started by gump, Jul 29, 2009.

  1. gump

    gump Guest

    I have over 5,000 rows of data. I only need the odd number rows (1,3,5) and
    need to delete the even rows. Is there a faster way then hold CTRL and
    clicking on even number rows and then deleting? How can select all the even
    number of rows and then delete all?
     
    gump, Jul 29, 2009
    #1
    1. Advertisements

  2. gump

    Glenn Guest

    gump wrote:
    > I have over 5,000 rows of data. I only need the odd number rows (1,3,5) and
    > need to delete the even rows. Is there a faster way then hold CTRL and
    > clicking on even number rows and then deleting? How can select all the even
    > number of rows and then delete all?


    Insert a column. Put =MOD(ROW(),2) in row 1 and copy down. Sort all of your
    data by that column. Delete the appropriate rows. Delete the column.
     
    Glenn, Jul 29, 2009
    #2
    1. Advertisements

  3. gump

    Ken Guest

    gump

    Put this formula in a spare column

    =mod(row(),2)

    copy it down all your relevant rows.

    convert the formula to values (paste special values on top of the mod
    formula)

    sort by that column, the zeros will come to the top where they can all
    be deleted at once

    the other rows should still be in the same order as prior to the sort
    since they are all 1's

    Good luck

    Ken
    Norfolk, Va



    On Jul 29, 3:41 pm, gump <> wrote:
    > I have over 5,000 rows of data.  I only need the odd number rows (1,3,5) and
    > need to delete the even rows.  Is there a faster way then hold CTRL and
    > clicking on even number rows and then deleting?  How can select all theeven
    > number of rows and then delete all?
     
    Ken, Jul 29, 2009
    #3
  4. gump

    Gord Dibben Guest

    You have a couple of suggestions but they both ibvolve sorting.

    Do you want to do that?

    If not, in an adjacent column in cell 2 enter an X

    In cell 4 enter an X

    Select cell 2 to cell 5

    Double-click to fill down.

    AutoFilter for X and delete those rows.


    Gord Dibben MS Excel MVP

    On Wed, 29 Jul 2009 12:41:02 -0700, gump <>
    wrote:

    >I have over 5,000 rows of data. I only need the odd number rows (1,3,5) and
    >need to delete the even rows. Is there a faster way then hold CTRL and
    >clicking on even number rows and then deleting? How can select all the even
    >number of rows and then delete all?
     
    Gord Dibben, Jul 30, 2009
    #4
  5. gump

    ammuma

    Joined:
    Mar 13, 2012
    Messages:
    1
    Likes Received:
    0
    Insert a column. Put =MOD(ROW(),2) in row 1 and copy down.
    Alternate rows will be filled with zeros and ones.
    Set filter to zero or one as required
    Only alternate rows will be visible
    press F5 and select visible cells
    press Ctrl+C to copy
    and Ctrl +V to paste to a new sheet.
     
    ammuma, Mar 13, 2012
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Conan Kelly

    Conditional formatting similar to shading alternating rows

    Conan Kelly, Aug 18, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    19
    Views:
    315
    Gord Dibben
    Aug 22, 2006
  2. Conan Kelly

    Conditional formatting similar to shading alternating rows

    Conan Kelly, Aug 18, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    179
    Conan Kelly
    Aug 18, 2006
  3. Guest

    How do I insert alternating blank rows in a list of data?

    Guest, Sep 18, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    10
    Views:
    298
    Don Guillett
    Sep 18, 2006
  4. happygal

    insert cells in alternating rows?

    happygal, May 30, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    214
    Reitanos
    May 30, 2008
  5. Daren

    Formatting Alternating Rows with a Color

    Daren, Sep 29, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    8
    Views:
    202
    Gord Dibben
    Sep 30, 2008
Loading...

Share This Page