I've recorded a macro in Excel that fills the columns with formulas. However, it's limited to the range I was using in my test data, i.e., it doesn't fill down to the last row of data when I use a different (longer) data set:
ActiveCell.FormulaR1C1 = "Term" Range("H2").Select ActiveCell.FormulaR1C1 = "=ROUND((RC[-1]-RC[-2])/30,0)" Range("H2").Select Selection.AutoFill Destination:=Range("H2:H11") Range("H2:H11").Select
If you can see above the range is limited to
H2:H11 – but I want it to autofill down to the last row of data populated in the dataset. This could be done by a check if column A is populated, but I can't find code that will work - nothing is simple enough for me to pick up.
Data is 10,000 rows with multiple columns, this particular piece of the macro is populating a formula into a blank column, but I only want to populate for the 10,000 rows. Column A has data in it, so how can I use this to define the range to autofill the formula down?
I've seen other answers on here refer to turning the data into a table in Excel – but I'm reluctant to do this as I'm dealing with a data set of 10,000+ rows and that adds a lot of extra formatting, etc.