How do I select all even or odd rows in Excel

Discussion in 'Microsoft Excel Misc' started by Guest, Oct 30, 2007.

  1. Guest

    Guest Guest

    In a large file, is it possible to select all even or odd rows quickly?
     
    Guest, Oct 30, 2007
    #1
    1. Advertisements

  2. Guest

    Ron Coderre Guest

    I think if you describe what you utlimately want to do with those rows, we can probably suggest a solution that will work for you.
    --------------------------

    Regards,

    Ron (XL2003, Win XP)
    Microsoft MVP (Excel)


    "mmcclain" <> wrote in message news:...
    > In a large file, is it possible to select all even or odd rows quickly?
     
    Ron Coderre, Oct 30, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Try this for even rows:

    Sub evens()
    Set r = ActiveSheet.UsedRange
    nLastRow = r.Rows.Count + r.Row - 1
    Set r1 = Cells(2, 1)
    For i = 4 To nLastRow Step 2
    Set r1 = Union(r1, Cells(i, 1))
    Next
    r1.EntireRow.Select
    End Sub

    --
    Gary''s Student - gsnu200752


    "mmcclain" wrote:

    > In a large file, is it possible to select all even or odd rows quickly?
     
    Guest, Oct 30, 2007
    #3
  4. Guest

    Guest Guest

    Maybe add an extra column with two values (0,1) for odd and even rows by
    entering 0,1,0,1 down the column. Extend the selection by selecting the first
    few values and double-clicking the fill handle (in the bottom right corner of
    the selection).

    Now you can use Data>Filter>Autofilter to select 0,1 to show alternate rows.

    "mmcclain" wrote:

    > In a large file, is it possible to select all even or odd rows quickly?
     
    Guest, Oct 30, 2007
    #4
  5. Guest

    MartinW Guest

    Hi Mclain

    One way,
    In a blank column put 0 in the first cell and 1 in the second
    Highlight both cells grab the fill handle and drag down
    as far as is needed, hold ctrl and let go of the left button.

    You can then use Autofilter to select 0 or 1.

    HTH
    Martin


    "mmcclain" <> wrote in message
    news:...
    > In a large file, is it possible to select all even or odd rows quickly?
     
    MartinW, Oct 30, 2007
    #5
  6. Guest

    Ron Coderre Guest

    Following the direction of other posts.....

    Try something like this:

    A1: 1
    A2: (blank)

    Select A1:A2
    Click and HOLD on the drag handle (black box in the lower right corner)
    Drag down as far as you need (eg through cell A100).
    (That will create a range of alternating numbers and blanks)

    While A1:A100 is still selected....
    Press [F5]..a shortcut for <edit><go to>
    Click [Special]
    Check: Blanks
    Click [OK]

    The even (blank) cells with be selected.

    or...
    Check: Constants
    Click [OK]
    to select the non-blank cells.

    Is that something you can work with?
    --------------------------

    Regards,

    Ron (XL2003, Win XP)
    Microsoft MVP (Excel)

    "Ron Coderre" <> wrote in message news:%...
    >I think if you describe what you utlimately want to do with those rows, we can probably suggest a solution that will work for you.
    > --------------------------
    >
    > Regards,
    >
    > Ron (XL2003, Win XP)
    > Microsoft MVP (Excel)
    >
    >
    > "mmcclain" <> wrote in message news:...
    >> In a large file, is it possible to select all even or odd rows quickly?

    >
    >
    >
     
    Ron Coderre, Oct 30, 2007
    #6
  7. Guest

    Guest Guest

    My suggestion is to use Advanced filter:

    Choose an empty helper column for criteria range, say Z!
    Leave empty Z1, enter formula in Z2
    =MOD(ROW(A2),2)=0 for even
    =MOD(ROW(A2),2)=1 for odd row No!

    Data/Filter/Advanced filter/Enter your used range as list range, Z1:Z2 as
    criteria range/click OK!

    Regards,
    Stefi




    „mmcclain†ezt írta:

    > In a large file, is it possible to select all even or odd rows quickly?
     
    Guest, Oct 30, 2007
    #7
    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. Guest
    Replies:
    0
    Views:
    193
    Guest
    Feb 9, 2005
  2. Guest
    Replies:
    1
    Views:
    320
    Bob Phillips
    Oct 17, 2005
  3. Replies:
    4
    Views:
    1,434
    Dave Peterson
    Jan 9, 2007
  4. Guest

    hiding even (or odd) rows in excel 2003

    Guest, Feb 2, 2007, in forum: Microsoft Excel Misc
    Replies:
    5
    Views:
    2,641
    Guest
    Feb 2, 2007
  5. HUGO

    Select even or odd numbers

    HUGO, Dec 25, 2008, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    583
    T. Valko
    Dec 26, 2008
Loading...

Share This Page