Creating a dynamic named range in Excel using VBA

Dynamic ranges automatically adjust their size based on the data, which can be incredibly useful for situations where data is constantly changing (such as in reports, dashboards, or real-time data processing).

What We Want to Do:

We want to create a dynamic named range using VBA that automatically adjusts its size when new data is added or removed. This way, the named range always includes all the relevant data, and formulas or charts linked to this range will update automatically.

Approach:

1. Use the Names.Add method to create a dynamic range.
2. Use the Offset and CountA methods to calculate the range dynamically.
3. Use Workbook or Worksheet objects to scope the range.

Detailed VBA Code:

Sub CreateDynamicNamedRange()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim DynamicRange As String   
    ' Set reference to the worksheet where you want to create the dynamic range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name   
    ' Find the last used row in Column A (or the column that you expect to always have data)
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Find the last used column in Row 1 (or the row that you expect to always have data)
    LastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   
    ' Define the dynamic range formula
    ' This creates a range from A1 to the last used cell (based on your data)
    DynamicRange = "Sheet1!$A$1:$" & ws.Cells(1, LastColumn).Address(False, False) & "$" & LastRow   
    ' Create the dynamic range by adding a name to the range
    ' Here, we add the dynamic range as a named range "MyDynamicRange"
    ThisWorkbook.Names.Add Name:="MyDynamicRange", RefersTo:="=" & DynamicRange   
    ' Optional: Confirm that the dynamic range has been created
    MsgBox "Dynamic range 'MyDynamicRange' has been created from A1 to " & ws.Cells(LastRow, LastColumn).Address  
End Sub

Explanation of the Code:

1. Set references:
   • The variable ws is used to reference the worksheet where the dynamic range will be created.
   • The LastRow and LastColumn variables are used to find the last used row and column in the worksheet.
2. Finding the last row and column:
   • LastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row: This line finds the last row with data in column A. It starts at the bottom of the worksheet and moves up until it finds data.
   • LastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column: This finds the last used column in row 1 by starting at the far-right of the row and moving left until it finds data.
3. Dynamic Range Formula:
   • The DynamicRange variable is used to construct the reference for the dynamic range in the format of Sheet1!$A$1:$[last used column][last row]. For example, if the data ends at column D and row 20, the range will be Sheet1!$A$1:$D$20.
4. Creating the Named Range:
   • ThisWorkbook.Names.Add Name:= »MyDynamicRange », RefersTo:= »= » & DynamicRange: This line creates a dynamic named range called « MyDynamicRange ». The RefersTo argument defines the range that the name refers to.
5. Confirmation:
   • The MsgBox function is used to display a message box to confirm that the dynamic range has been successfully created.

How It Works:

• Dynamic Adjustment: When you add new data to the worksheet, the named range « MyDynamicRange » will automatically adjust to include the new rows and columns based on the data’s current size.
• Real-time Updates: This dynamic range is updated every time the workbook is opened, or when new data is added and the macro is rerun.

How to Use:

1. Open the workbook where you want to create a dynamic range.
2. Press ALT + F11 to open the VBA editor.
3. In the VBA editor, insert a new module by clicking Insert > Module.
4. Paste the provided code into the module.
5. Run the macro CreateDynamicNamedRange to create the dynamic range.

You can use this named range in formulas or charts, and it will automatically update based on the size of your data.

Possible Modifications:

• Different Columns/Rows: You can modify the code to target different columns or rows. For example, if your data starts from column B instead of A, change ws.Cells(ws.Rows.Count, « A ») to ws.Cells(ws.Rows.Count, « B »).
• Multiple Ranges: If you want to create multiple dynamic ranges, you can replicate the logic inside a loop or as separate named ranges, each with their own row/column calculations.