SOLVED

Dynamic autofill using macros

Copper Contributor

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?

3 Replies
best response confirmed by kimpy318 (Copper Contributor)
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.

@NikolinoDE 

 

Hello! I have recorded a macros code, and I was wondering would you declare the lastRow variable before the macros recorded code and then, at the end of the code that is recorded by macros, would you add the following code in the screenshot? I have changed "C" to "D" because the data that I am dealing with is in the "D" column. 

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

 

@JonesGraceV 

To integrate the dynamic autofill functionality into your recorded macro, you can declare the lastRow variable at the beginning of your macro and then add the dynamic range code at the appropriate place within your existing recorded code. Below is an example of how you can modify your recorded macro to include this dynamic autofill feature.

Let us assume your original recorded macro looks something like this:

Vba Code is untested backup your file.

Sub RecordedMacro()
    ' Your recorded macro code here
    Range("D2:D13").Copy
    Range("C2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    ' More recorded macro code here
    Range("A2").FormulaR1C1 = "=some_formula"
    Range("D2").FormulaR1C1 = "=some_other_formula"
End Sub

Now, you can modify it to include the dynamic autofill for columns A and D based on the last row of data in column D:

Vba Code is untested backup your file.

Sub RecordedMacro()
    Dim lastRow As Long
    
    ' Your recorded macro code here
    Range("D2:D" & lastRow).Copy
    Range("C2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    ' Find the last row in Column D
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    ' Fill formulas in columns A and D based on the last row in Column D
    Range("A2:A" & lastRow).FillDown
    Range("D2:D" & lastRow).FillDown
    
    ' More recorded macro code here
    Range("A2").FormulaR1C1 = "=some_formula"
    Range("D2").FormulaR1C1 = "=some_other_formula"
End Sub

Explanation:

  1. Declare lastRow: The lastRow variable is declared at the beginning of the macro.
  2. Find the last row in Column D: The lastRow variable is set to the last used row in column D using Cells(Rows.Count, "D").End(xlUp).Row.
  3. Adjust Autofill Ranges: The autofill ranges for columns A and D are set from row 2 to the lastRow value.
  4. Integrate with Existing Code: The dynamic autofill code is placed at the appropriate location within the existing recorded macro to ensure that the formulas are applied correctly.

Make sure to replace "=some_formula" and "=some_other_formula" with the actual formulas you want to apply in your macro.

By integrating these changes, your macro will now dynamically adjust the autofill range based on the size of the data in column D, making it more flexible and adaptable to different data set sizes.

The text, steps and codes were created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

1 best response

Accepted Solutions
best response confirmed by kimpy318 (Copper Contributor)
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.

View solution in original post