Results 1 to 6 of 6

VBA to copy down formula to last row of data

This is a discussion on VBA to copy down formula to last row of data within the Excel Questions forums, part of the Question Forums category; Hi All, I have a large spreadsheet with thousands of rows (Using Excel 2007) I need to copy down a ...

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    26

    Default VBA to copy down formula to last row of data

    Hi All, I have a large spreadsheet with thousands of rows (Using Excel 2007)

    I need to copy down a formula from cell I2 to the last row that contains data (the number of rows will vary from month to month), is there a quick way of doing this rather than dragging the formula down (which can take a while depending on the number of rows).

    I have a pice of code below which copies the cell above but just copies the value & not the formula

    Private Sub CommandButton3_Click()
    Dim MyRange As Range, c As Range
    On Error Resume Next
    Set MyRange = Range("I:I").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not MyRange Is Nothing Then
    MyRange.FormulaR1C1 = "=R[-1]C"
    For Each c In MyRange
    c.Value = c.Value
    Next c
    End If
    End Sub


    Thanks in advance for your help.

    Kind Regards
    J

  2. #2
    Board Regular
    Join Date
    Aug 2009
    Posts
    60

    Default Re: VBA to copy down formula to last row of data

    Did you try double clicking the lower right hand corner of the cell with the formula in it? It causes the formula to copy down to the first blank cell..

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,247

    Default Re: VBA to copy down formula to last row of data

    Try

    Code:
    Private Sub CommandButton3_Click()
    Dim LR As Long
    LR = ActiveSheet.UsedRange.Rows.Count
    Range("I2").AutoFill Destination:=Range("I2:I" & LR)
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    239

    Default Re: VBA to copy down formula to last row of data

    You can use the code below to deteremine the lastrow

    dim lastrow as long
    long = cells(rows.count,1).end(xlup).row

    This will look in column a and work out how many rows there are. If column a doesn''t go to the bottom of the data then change the number after "rows.count," to the column number

    You can then apply the formula as follows:

    Range("i2:i" & lastrow).formular1c1 = "yourformula"

    Peter

  5. #5
    New Member
    Join Date
    Jan 2010
    Posts
    26

    Default Re: VBA to copy down formula to last row of data

    Thanks guys, they all work. Knew about double clicking the bottom right hand corner but am trying to up my VBA skills (which is going to take me an age!!!)

  6. #6
    New Member
    Join Date
    Jul 2014
    Posts
    19

    Default Re: VBA to copy down formula to last row of data

    This was very helpful for me. I'm using Excel 2010, so I had to kinda of morph the two solutions into one, but it worked! Thanks!

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