Last week, I encountered a situation where one of my developers needed to populate an Excel Template with 100+ values from a SQL query using a C# program. There was an existing template in place that receives these values for a report distribution. Issues were coming up when users began making updates (inserting rows, deleting rows, etc.) to the template, and the mapping in the code no longer worked because these values were coded directly to a cell reference in the C# program (I.e. Set Cell(B6) equal to value X). As soon as the template was modified, the mapping got thrown off. Without updating the source code, the template would never be updated correctly. If the user inserted a new row between rows 5 & 6, the cell reference in the code would still populate Cell(B6) with value X, but it would no longer be correct (should now go to Cell(B7) due to the inserted row.
This is where Named Ranges come in handy. Please see a very informative post on Excel Named Ranges here: 20 tips for Named Ranges in Excel
Named ranges adjust when deleting and inserting cells within a worksheet
When you delete *part* of a named range, or if insert cells/rows/columns inside a named range, the range reference will adjust accordingly and remain valid. However, if you delete all of the cells that enclose a named range, the named range will lose the reference and display a #REF error.
Also, if you insert cells/rows/columns around your named range, the range reference will still adjust accordingly and remain valid, which fixed the issue we were having (noted at beginning of post). This is the issue that impacted our process, and I’ll run through steps on how to solve this problem in the content below.
Excel VBA – Create Multiple Named Ranges Based on List – Setup
Once we decided on adding named ranges, there were a lot of cells that needed to be setup (100+). I decided to create a table to build the Named Range references, then loop through each table row to build the Named Ranges on the target sheet. In this post, I will show you how to accomplish this task, with a smaller list of Named Ranges for simplicity.
Here is the macro code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Sub CreateMultipleNamedRangesBasedOnList() Dim srcrng As Range Dim celltomap As Range Dim rng As Range Dim RangeName As String Dim CellName As String Worksheets("Ranges").Activate Set srcrng = Worksheets("Ranges").Range("A2", ActiveSheet.Range("A2").End(xlDown)) Worksheets("Main").Activate For Each cell In srcrng RangeName = cell.Value CellName = cell.Offset(0, 1).Value Set celltomap = Worksheets("Main").Range(CellName) Worksheets("Main").Names.Add Name:=RangeName, RefersTo:=celltomap Next cell End Sub |
Refer to the following post to get many more good examples of how to work with Named Ranges in VBA: The VBA Guide To Named Ranges
In Excel, create/name two tabs: “Main” and “Ranges”
In the “Main” tab, add following table:
In the “Ranges” tab, add following table:
Run the macro above, then press keyboard shortcut “Ctrl + F3” to view the newly created Named Ranges:
Now, Insert a column between A & B in the “Main” tab.
Then, Insert a row between rows 4 & 5 in the “Main” tab.
The “Main” tab will now look like this:
However, your Named Range references remain in tact. Press keyboard shortcut “Ctrl + F3” to view the Named Ranges:
As you can see, the “Refers To” cell references automatically update after the spreadsheet changes (inserted column and row)! This is a great example where Named Ranges come to the rescue, and prevents further updates to the C# value mapping program.
Please modify the code to suit your needs.
Additional Content
Check out more examples by visiting my Home Page
Here you will find topics covering Qlikview SQL Server Excel VBA
Hi,
How do I change the scope to global in the code?
Thanks
Hello Michael, use the code below:
Sub CreateMultipleNamedRangesBasedOnList()
Dim srcrng As Range
Dim celltomap As Range
Dim rng As Range
Dim RangeName As String
Dim CellName As String
Worksheets("Ranges").Activate
Set srcrng = Worksheets("Ranges").Range("A2", ActiveSheet.Range("A2").End(xlDown))
Worksheets("Main").Activate
For Each cell In srcrng
RangeName = cell.Value
CellName = cell.Offset(0, 1).Value
Set celltomap = Worksheets("Main").Range(CellName)
' Local - Range only applies to Worksheet
'Worksheets("Main").Names.Add Name:=RangeName, RefersTo:=celltomap
' Global - Range applies to entire Workbook
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=celltomap
Next cell
End Sub