Dynamic autofill using macros

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 😧

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.