Create dynamic named ranges in Excel using VBA

Step-by-Step Guide to Creating Dynamic Named Ranges in Excel VBA

Step 1: Open the Visual Basic for Applications (VBA) Editor

To access the VBA editor:

• Press Alt + F11 on your keyboard, or click on the Developer tab in Excel (if enabled) and then click on Visual Basic.
• This will open the VBA editor where you can write your VBA code.

Step 2: Insert a Module

A Module is where you will insert your VBA code.

• In the VBA editor, go to the Insert menu at the top and select Module.
• A new blank module will appear, where you can type your code.

Step 3: Write the VBA Code

Now, let’s write the code for creating dynamic named ranges.

Code Example:

Sub CreateDynamicNamedRange()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim lastCol As Long   
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last used row and column in the sheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   
    ' Set the range for the dynamic range (change the starting point and range as needed)
    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))   
    ' Create a dynamic named range
    ThisWorkbook.Names.Add Name:="DynamicRange", RefersTo:=rng
End Sub

Explanation of the code:

1. Define the worksheet and range objects:

• Dim ws As Worksheet
• Dim rng As Range
• Dim lastRow As Long
• Dim lastCol As Long

1. • We declare variables to store references to the worksheet, the range, and the last row/column of the data.
2. Set the worksheet to the one you want to work with:

• Set ws = ThisWorkbook.Sheets(« Sheet1 »)

• • We specify which worksheet we are working with (replace « Sheet1 » with the name of your sheet).

3.Find the last used row and column in the sheet:

• lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row
• lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• • lastRow: This finds the last row with data in column « A ».
  • lastCol: This finds the last used column in row 1.

4.Define the dynamic range:

• Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

• • This defines the dynamic range that starts from cell A1 and extends to the last used row and column.

5.Create the named range:

• Names.Add Name:= »DynamicRange », RefersTo:=rng

1. • We add a named range to the workbook and assign the dynamic range to it. The name of the range is DynamicRange, but you can change this to whatever name you prefer.

Step 4: Run the Macro

To run the macro:

1. Go back to Excel.
2. Press Alt + F8, select the CreateDynamicNamedRange macro, and click Run.

Step 5: Verify the Named Range

To check if the named range was created successfully:

• Go to the Formulas tab in Excel.
• Click on Name Manager.
• Look for DynamicRange in the list of named ranges.
• If it’s there, the dynamic named range has been created successfully.

Output:

When the macro is run, it will create a dynamic named range named « DynamicRange » that adjusts automatically as you add or remove data. The named range will always refer to the data in the range starting from A1 and extending to the last used row and column.

Explanation:

A dynamic named range is one that automatically expands or contracts as data is added or removed. This VBA script makes it possible to define such a range using the Names.Add method. The range it refers to (from cell A1 to the last used row and column) will update whenever the worksheet is modified. By doing this programmatically, you can automate the creation of dynamic ranges, which is particularly useful for data analysis, creating charts, or automating other tasks that require a dynamic data range.