Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COPY FORMULA .END(xlDOWN) (EXCEL 97)

    Hi All,

    Please help again. How do I copy a formula to the last row which is the last row of the left hand cell. The formula column is empty, and I don't want to copy the formula end(xldown) to row 65536.

    A small example attached.

    Thanks very much.
    Attached Files Attached Files

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

    Re: COPY FORMULA .END(xlDOWN) (EXCEL 97)

    Hi Again

    I found a possible answer for my own question, but this will only work if NO other blank cells are in the range.

    Range("D1").Select
    Selection.Copy
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    End Sub

    There must be a more clever way. Please help.

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

    Re: COPY FORMULA .END(xlDOWN) (EXCEL 97)

    How about:

    <pre> Range("D1", Range("C65536").End(xlUp).Offset(0, 1)).FormulaR1C1 = _
    "=SUM(RC[-2]:RC[-1])"
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COPY FORMULA .END(xlDOWN) (EXCEL 97)

    Hi Rene:
    Maybe I am missing something. Do you have to do this with VBA? If not, the quickest way to do this is to simply double click on the bottom right corner of cell D1 and it will copy it down the length of column C
    Stephen

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

    Re: COPY FORMULA .END(xlDOWN) (EXCEL 97)

    Hi Legare & Stephen,

    Thank you very much for the reply. Legare's formula works 100% as I had to use VBA to automate the function.

    Thanks again.

Posting Permissions

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