Forum Discussion

kimpy318's avatar
kimpy318
Copper Contributor
Jun 07, 2023
Solved

Dynamic autofill using macros

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 colum...
excel
Formulas and Functions
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Jun 07, 2023

    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's avatar
NikolinoDE
Gold Contributor
Nov 13, 2024

Code Example:

Sub RecordedMacro()
    Dim lastRow As Long
    
    ' Step 1: Original recorded macro code
    ' Assuming this is your recorded action, copying values from D2:D13 to C2
    Range("D2:D13").Copy
    Range("C2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    ' Step 2: Calculate lastRow based on Column D (where your data is located)
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    ' Step 3: Autofill columns A and D based on lastRow
    Range("A2:A" & lastRow).FillDown
    Range("D2:D" & lastRow).FillDown
    
    ' Step 4: Reapply the formulas in A2 and D2
    ' Replace "=some_formula" and "=some_other_formula" with the actual formulas you want in A2 and D2
    Range("A2").FormulaR1C1 = "=some_formula"  ' Example: Replace with your actual formula for A2
    Range("D2").FormulaR1C1 = "=some_other_formula"  ' Example: Replace with your actual formula for D2
    
    ' Step 5: Any additional code from your original recorded macro can go here
    ' For example, additional formatting or other operations after autofilling
    
End Sub

 

Resources