VBA AutoFill Varying Rows of Data

[DBCox]

Hello everyone,

I have an Excel workbook that has multiple sources of data that vary depending on the day. I am running into a problem with the equations refering the data giving me a #REF error when the number of data rows decreases. This is fairly easily corrected manually by selecting the top row of columns that contain equations and "dragging" them down to correct all of the references. The problem is, I need to do this with a macro. The following works for 16 rows of data, but obviously it will not work for varying rows of data. Furthermore, I am working with anything from 15,000 to 25,000 rows of data.

    Range("D1:E1").Select    Selection.AutoFill Destination:=Range("D1:E16"), Type:=xlFillDefault    Range("D1:E16").Select    Range("H12").Select

One solution is changing the range to "D1:E25000". The only issue with this is it will often AutoFill more equations than necessary and has potential to cause errors due to lack of data.Ideally, I could change the number preceding the "E" depending on the number of rows of data. I can easily get that number with a CountA function, but cannot seem to get the

Selection.AutoFill Destination:=Range("D1:E16"), Type:=xlFillDefault

line to work with a variable in the parenthesis.How can I easily vary the number of rows? Also, is there a way to directly pull the CountA value with VBA, rather than putting the equation in a cell and having VB pull the cell's value?

Thanks!

David

 

[p45cal]

LR=cells(rows.count,"A").end(xlup).row
Range("D1:E1").AutoFill Destination:=Range("D1:E" & LR), Type:=xlFillDefault

In the first line, substitute for "A" the column letter which is already full down to to.where yonpu want the formulawe autofilled do

 

[DBCox]

p45cal,

Thank you for the info. It worked perfectly!