Copy formulas to last row

[johnnyb5]

I have a large spreadsheet in which I have used a macro to write different formulas from I2-R2. The formulas work fine as they are. However, I have to manually select the cells and drag them to the last row to complete my data calculations. The quantity of rows will change daily with different data downloads. Could anyone help me with some code to find the last row and automate coping the formulas form the second row to the last row?

Also, columns A-H data may not end on the same row but my formulas must go to the last row with data. I've reviewed the "Live Lessons" several times but can't seem to figure this part out.

Thanks,
JB

 

[MARK858]

Not sure I am reading your description right but try the code below and comment.

Sub AddFormula()
    Dim LstRw As Long
    LstRw = Range("A:H").Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    Range("L2:R2").AutoFill Range("L2:R" & LstRw)
End Sub

 

[johnnyb5]

Mark,
You hit the nail on the head. It worked perfectly!!
If you had time to explain that code it would be a bonus.
I appreciate your time and effort for sure.
Thanks again!!!
JB

 

[MARK858]

    [color=darkblue]Dim[/color] LstRw [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=green]' declares the LstRw variable as Long which is a whole number [URL="http://www.ozgrid.com/VBA/variables.htm"]see this link[/URL][/color]
    
    LstRw = Range("A:H").Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    [color=green]'Range("A:H") is columns A:H[/color]
    [color=green]' "*" the * is a wildcard[/color]
    [color=green]' SearchOrder:=xlRows is the order it searches in[/color]
    [color=green]' SearchDirection:=xlPrevious means it searches bottom to top'[/color]
    [color=green]' LookIn:=xlValues means it doesn't include formulas producing an empty string i.e. ""[/color]
    [color=green]' .Row is the row number[/color]
    [color=green]'  [URL="http://msdn.microsoft.com/en-us/library/office/ff839746(v=office.14).aspx"]this link for an overview of the Find.range method[/URL][/color]
    
     Range("L2:R2").AutoFill Range("L2:R" & LstRw)
     [color=green]'Range("L2:R2").AutoFill = the range to fill down[/color]
     [color=green]'Range("L2:R" & LstRw) the destination range using the row number variable from the find.[/color]
     [color=green]'The range must start with the same range being used for the fill down.[/color]

 

[johnnyb5]

Thanks again Mark!!
I appreciate your time. I've got some research on my schedule now that you've provided an interesting link.

JB

 

[MARK858]

2 Links