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.

JonesGraceV's avatar
JonesGraceV
Copper Contributor
Jul 16, 2024

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

 

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jul 17, 2024

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.

Resources