Dynamic autofill using macros

[kimpy318]

Hello all, 

Does anyone know how to set a range so that it dynamically autofills a column?

When recording my macro, I initially used a data set of size 12, in which I copied and pasted one column of info into Column C (more specifically C2:C13). I then auto populated A2:A13 and D2:D13 with equations so so that it was the same size as column C by double clicking the bottom right corners of A2 and D2. This seemed to work until I tested the macro on a data set of size 20, which correctly copied the info into Column C, but only auto populated columns A and D to A13 and D13 (when it should have gone to A21 and D21). The VBA editor seems to now have a fixed range on rows 2 to 13, and it seems really gruesome to update this range every time I run my macro on a new data set. How can I fix this?

[#2 · Accepted solution]

@kimpy318 

To make the autofill range dynamic in your VBA macro, you can modify the code to determine the last row of data in Column C and adjust the autofill range accordingly. Here's an example:

Sub AutoFillData()
    Dim lastRow As Long
    
    ' Find the last row in Column C
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    ' Autofill columns A and D based on the last row in Column C
    Range("A2:A" & lastRow).FillDown
    Range("D2:D" & lastRow).FillDown
End Sub

Code untested.

In this code, the lastRow variable is used to store the last row with data in Column C. By using Cells(Rows.Count, "C").End(xlUp).Row, we can find the last used row in Column C dynamically.

Then, the autofill range for columns A and D is set from row 2 to the lastRow value. This ensures that the autofill will adjust based on the size of the data in Column C.

By using this dynamic approach, your macro will correctly autofill columns A and D based on the number of rows in Column C, regardless of the data set size.

You can customize and integrate this code into your existing macro or create a new macro with this code snippet.