Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Seeking better way to 'fill" formulas (Excel 2000 SR-1)

    I do a lot of interactive data analysis which involves something like the following:

    1. Import a .csv data file with unknown (> 5000) rows and several columns;

    2. Create additional columns which contain formulas referring to the data ( C1 =A1+ B1,C2 = A2+B2, etc)

    My usual style is to enter the formula in the first row and then do a click-select down the column to fill the formula to the row which matches the end of the data column "to the left". This gets very tedious when there are some number of thousands of rows.

    So: I would guess there's a better less tedious way to do this. Any suggestions ?

    TIA

    Bill Meier

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seeking better way to 'fill" formulas (Excel 2000 SR-1)

    Bill select the cells that you want the formula in first, then type the formula into the first cell and hit ctrl+enter. This will copy the formula into all of the highlighted cells. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seeking better way to 'fill" formulas (Excel 2000 SR-1)

    Thanks

    I didn't express myself clearly. What I'm looking for is a way to somehow avoid doing the select of 1000's of cells.

    I essentially want to "fill" a column to the row matching last data row in a column "to the left".

    (Now that I'm thinking about this a bit more, maybe it wouldn't be very hard to write a macro (using count, etc)).

    Bill

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seeking better way to 'fill" formulas (Excel 2000 SR-1)

    Enter the formula in the top cell. Then double click on the fill handle in the lower right corner of the cell. That will fill the formula down as far as there is something in the cells in the column to the left.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seeking better way to 'fill" formulas (Excel 2000 SR-1)

    Ouch... (It's that simple!).

    Thanks

    Bill

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seeking better way to 'fill" formulas (Excel 2000 SR-1)

    Hi Legare,

    That is really excellent, but how do you use this in a macro to automatically expand the range.

    Sub Macro1()
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
    Selection.AutoFill Destination:=Range("C1:C8") .................................. expand the range
    Range("C1:C8").Select
    End Sub

    Thanks a lot

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seeking better way to 'fill&quot; formulas (Excel 2000 SR-1)

    Something like this to exactly duplicate the double click:

    <pre>Sub Macro1()
    Dim lLastRow As Long
    lLastRow = Range("B1").End(xlDown).Row
    Range("C1").FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("C1").AutoFill Destination:=Range("C1:C" & lLastRow)
    End Sub
    </pre>


    To fill all the way to the last entry in column B if there are empty cells:

    <pre>Sub Macro1()
    Dim lLastRow As Long
    lLastRow = Range("B65536").End(xlUp).Row
    Range("C1").FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("C1").AutoFill Destination:=Range("C1:C" & lLastRow)
    End Sub
    </pre>

    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seeking better way to 'fill" formulas (Excel 2000 SR-1)

    100 % THANKYOU VERY MUCH

Posting Permissions

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