Macro to autofill columns in Excel

Question

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.

Solution

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.

Comments

[Scott - Слава Україні]

Have you tried to code a check if column A is populated?  If so, please post it and describe what it did.  Also, please explain in a little more detail what your data look like, and what you want the macro to do (i.e., what the data look like before and after the macro runs). And do you really believe that the [worksheet-function] tag is relevant to this question? Please do not respond in comments; edit your question to make it clearer and more complete.

[newbie]

Hi @Scott I've edited the question as you suggested, please let me know if any further clarifications are needed.