Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Fill Series & deleting alternate rows

  1. #1
    New Member
    Join Date
    Mar 2004
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Fill Series & deleting alternate rows

    I want to copy links to another spreadsheet down a column. The info I'm looking for is located in every other cell. ie F3, F5, F7. I want to fill the series (=$F3, =$F5, =$F7) automatically to save on typing. Fill series will do this with real numbers 3, 5, 7. Can it be down with a formula?

    Also, is there a way to delete every other row with minimal keystrokes? Selecting with ctrl+mouseclick gets tedious when there is HUNDREDS to delete!

    many tks[/img]

  2. #2
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi - Welcome to the board

    Put this in Row 1 of any column (besides F) and fill down

    =INDIRECT("F" & 2*ROW()-1)

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    NY
    Posts
    1,031
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fill Series & deleting alternate rows

    Enter F3 & F5, then fill down via the fill handle (without the "="). You'll get F3, F5, F7, F9, etc. Select all, and edit/replace F with =F.

    to delete every other row, in an unused column enter 1, x, 2, x in the first 4 rows, then use the fill handle as far down as necessary. The series will repeat, 3, x, 4, x, etc. Sort everything by this column and all the x's will be together. You can delete all these rows, then clear the column.

  4. #4
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To delete every other row:
    Code:
    Option Explicit
    
    Sub Test()
    
    Dim x As Long
    Dim LastRow As Long
    
        LastRow = Range("A65536").End(xlUp).Row
        For x = LastRow To 1 Step -2
            Rows(x & ":" & x).Delete
        Next x
        
    End Sub

  5. #5
    New Member
    Join Date
    Mar 2004
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fill Series & deleting alternate rows

    Excellent answers! Thank-you very much.

    I haven't much experience using code, I'll have to learn. But the other responses make sense.

    Natalie

  6. #6
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your Welcome

    Take Care

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com