Forum Discussion
kimpy318
Jun 07, 2023Copper Contributor
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?
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.
- IanFCopper Contributor
Hi, That's great thanks, appreciate your help, will try it shortly. Thanks again Ian
- NikolinoDEGold 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
- IanFCopper 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,
- NikolinoDEGold Contributor
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.
- JonesGraceVCopper Contributor
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
- NikolinoDEGold Contributor
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:
- Declare lastRow: The lastRow variable is declared at the beginning of the macro.
- 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.
- Adjust Autofill Ranges: The autofill ranges for columns A and D are set from row 2 to the lastRow value.
- 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.