How to Fill a Named Range Down with Excel VBA
Question details
The user wants to use an Excel VBA macro to repeatedly copy a named range into a destination column, adjusting the fill area dynamically based on the last used row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the repetitive task of duplicating a specific named range of cells downwards to match the length of an adjacent data column.
- Observed behavior
- Needs a reliable VBA script to dynamically calculate the destination range based on the last used row and automatically fill the named range data into it without manual dragging.
Ensure that the Developer tab is enabled in your spreadsheet ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) so your VBA code is retained.
Use a VBA Macro with Custom Lists to AutoFill the Named Range
This method temporarily registers your named range as a Custom List in Excel, allowing you to use the standard AutoFill method to repeat the sequence down to the last used row.
By converting the named range into a custom list, the VBA AutoFill method will seamlessly repeat the exact sequence of your named range through the entire destination area. The macro cleans up after itself by deleting the custom list once the fill operation is complete.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the top menu, click 'Insert' and select 'Module' to create a new blank script window.
Copy and paste the macro code into the module. You will need to define 'LastRow', temporarily add the named range using 'Application.AddCustomList ListArray:=Range("YourNamedRange")', apply 'AutoFill' to the destination, and finally delete the custom list.
Update the code to match your worksheet. Change the reference used to find the last row (e.g., 'K3') and the destination starting cell (e.g., 'L4').
Press F5 or click the 'Run' button in the toolbar to execute the script. The named range will automatically fill down the designated column.
Use WPS Spreadsheet Macro Editor to Fill Named Ranges
WPS Office provides robust built-in support for VBA and Macros, allowing you to automate repetitive tasks like filling named ranges with high compatibility and excellent performance.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet application.
- 2. Access Developer Tools: Navigate to the 'Tools' tab and click on 'VBA Editor' to open the macro environment.
- 3. Insert Your Code: Go to Insert > Module and paste your Excel VBA code directly into the editor.
- 4. Execute the Macro: Run the macro to automatically fill your named ranges, enjoying seamless cross-compatibility without requiring modifications to your code.
Frequently Asked Questions
Why does my macro return a 'Subscript out of range' error?
This error typically occurs if the named range specified in your VBA code does not exist in the workbook. Check the Name Manager (Formulas > Name Manager) to verify that your named range is spelled exactly as it is in the script.
How can I find the last used row in a column dynamically using VBA?
You can find the last used row by looking from the bottom up. Use a command like `Cells(Rows.Count, "A").End(xlUp).Row`, replacing "A" with your target column letter. This is often more reliable than using `xlDown` if there are blank cells in your data.
Will the custom list created by the macro stay in my Excel settings permanently?
No. As long as your script includes the line `Application.DeleteCustomList Application.CustomListCount` at the end, the temporary list will be removed immediately after the AutoFill operation finishes.
Can I fill multiple columns at once using this VBA method?
Yes, you can modify the AutoFill destination range to span multiple columns (e.g., `Range("L4:M" & LastRow)`), provided your starting range is also properly sized across those columns.
