Excel VBA – Create Multiple Named Ranges Based on List

by

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:

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:

Excel VBA - Create Multiple Named Ranges Based on List

In the “Ranges” tab, add following table:

Excel VBA - Create Multiple Named Ranges Based on List

Run the macro above, then press keyboard shortcut “Ctrl + F3” to view the newly created Named Ranges:

Excel VBA - Create Multiple Named Ranges Based on List

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:

Excel VBA - Create Multiple Named Ranges Based on List

However, your Named Range references remain in tact.  Press keyboard shortcut “Ctrl + F3” to view the Named Ranges:

Excel VBA - Create Multiple Named Ranges Based on List

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

 

 

2 thoughts on “Excel VBA – Create Multiple Named Ranges Based on List”

    • 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

      Reply

Leave a Comment