Macro to autofill columns in Excel

macrosmicrosoft excelmicrosoft-excel-2007vba

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.

Best Answer

You may try ActiveSheet.UsedRange.Rows.Count which returns the last row number of the current worksheet. While it does not check row A, it is a neat way to fill data down to the last row.

For example

lastRow = ActiveSheet.UsedRange.Rows.Count
' ... something else ...
Selection.AutoFill Destination:=Range("H2:H" & lastRow)

You can replace the Activesheet with a Worksheet object if you wish to specify a worksheet to work on.

Related Question