Always Autofill formula to the end of List

[holyasian]

To all,

I am trying to make a macro that autofills a formula to the end of a specified range. This is great, but the data is always changing so there will frequently be a different number of rows. I've spent the last hour Google searching a solution but none apply to my situation. Here is the VBA-coding

Sub Diff()
'
' Diff Macro
'

'
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-2]"
Range("I2:J2").Select
Selection.AutoFill Destination:=Range("I2:J213")
Range("I2:J213").Select

End Sub

I think the main problem is that the formula applys to two columns, so all of the other internet solutions didn't work. If you need more specification, let me know.

Thanks!

[kieranbop]

Please Login or Register  to view this content.

this should do the trick, instead of you having to name your ranges

[holyasian]

Hmm, I copy and pasted your formula exactly into VBA. My Sheet was already named Sheet 1, but it didnt work. It says the Autofill method of range class failed.

[kieranbop]

try again, I've editted my original post after re-reading your question

[holyasian]

I think you may be on to something, but it actually took the formula into my header instead of going down. Would it make a difference if I have a macro that subtotals my values before this step?

[holyasian]

Actually I just found that by changing the .End(xlUp) to .(xlDown), the macro works, but it applys it all the way to the bottom of my spreadsheet. Is there way to just limit it to the bottom of my data instead of the entire sheet?

[kieranbop]

to limit it to the bottom of your data yes, the lastRow function

so whatever cell you have data in , I was assuming "I" it will go down and fill aslong as I goes down.

so if I is 23, it will fill down 23. if that makes any sense?

[holyasian]

Yes that makes sense. It ended up working fine once I combined it with some other coding I found on the web. Thanks for your help.