Forum Discussion

kimpy318's avatar
kimpy318
Copper Contributor
Jun 07, 2023

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 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?

  • 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.

  • IanF's avatar
    IanF
    Copper Contributor

    Hi, That's great thanks, appreciate your help, will try it shortly. Thanks again Ian

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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

     

  • IanF's avatar
    IanF
    Copper Contributor

    Hi, I tried this with a Macro I was using, but it doesn't quite work, If possible could you have a look at my formula and let me know what is wrong, the Macro works but not the dynamic addition, see screen shot of the Macro,

     

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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

      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

        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