Create Dynamic Range Support with Excel VBA

VBA Code for Creating Dynamic Range Support

Option Explicit

' This procedure creates a named dynamic range based on the data in a specified column.
Sub CreateDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rngName As String
    Dim rngAddress As String
    Dim dynamicRange As Range
    ' Define the worksheet where the range is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    ' Define the range name
    rngName = "DynamicDataRange" ' Change to your desired range name
    ' Find the last row in column A with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Define the dynamic range address (assuming column A)
    rngAddress = ws.Range("A2:A" & lastRow).Address(True, True, xlA1, True)
    ' Set the range object
    Set dynamicRange = ws.Range("A2:A" & lastRow)
    ' Create or update the named range in the workbook
    On Error Resume Next
    ThisWorkbook.Names(rngName).Delete ' Delete existing name if it exists
    On Error GoTo 0
    ' Add a new named range
    ThisWorkbook.Names.Add Name:=rngName, RefersTo:=dynamicRange
    ' Notify the user
    MsgBox "Dynamic range '" & rngName & "' created successfully at " & rngAddress, vbInformation, "Dynamic Range Created"
End Sub

Detailed Explanation of the Code

1. Declaring Variables

Dim ws As Worksheet

Dim lastRow As Long

Dim rngName As String

Dim rngAddress As String

Dim dynamicRange As Range

• ws: Stores the reference to the worksheet where the data is located.
• lastRow: Stores the last row number that contains data in column A.
• rngName: Stores the name of the dynamic range.
• rngAddress: Stores the address of the dynamic range.
• dynamicRange: Represents the actual range object.

2. Setting the Worksheet Reference

Set ws = ThisWorkbook.Sheets(« Sheet1 ») ‘ Change to your sheet name

• We set the ws variable to reference the worksheet « Sheet1 ».
• Change « Sheet1 » to match your actual worksheet name.

3. Defining the Named Range

rngName = « DynamicDataRange »

• This sets the name of the dynamic range.
• You can change « DynamicDataRange » to any preferred name.

4. Finding the Last Row with Data

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• This line finds the last non-empty row in column A.
• ws.Cells(ws.Rows.Count, 1).End(xlUp).Row:
  • ws.Rows.Count gets the total number of rows in the sheet.
  • .End(xlUp) moves upward from the last row to find the last cell with data.

5. Defining the Dynamic Range Address

rngAddress = ws.Range(« A2:A » & lastRow).Address(True, True, xlA1, True)

• ws.Range(« A2:A » & lastRow): Creates a range from A2 to the last row with data.
• .Address(True, True, xlA1, True): Converts the range to an absolute address format.

6. Assigning the Range to an Object

Set dynamicRange = ws.Range(« A2:A » & lastRow)

• This assigns the identified range to the dynamicRange variable.

7. Handling Existing Named Ranges

On Error Resume Next

ThisWorkbook.Names(rngName).Delete ‘ Delete existing name if it exists

On Error GoTo 0

• On Error Resume Next: Prevents errors if the named range does not exist.
• ThisWorkbook.Names(rngName).Delete: Deletes an existing named range if found.
• On Error GoTo 0: Resets normal error handling.

8. Creating the Named Range

ThisWorkbook.Names.Add Name:=rngName, RefersTo:=dynamicRange

• This adds a new named range to the workbook.
• The range is now dynamic and updates as the data grows or shrinks.

9. Displaying a Confirmation Message

MsgBox « Dynamic range ‘ » & rngName & « ‘ created successfully at  » & rngAddress, vbInformation, « Dynamic Range Created »

• A message box notifies the user that the named range has been successfully created.

How to Use the Code

1. Open Excel and press ALT + F11 to open the VBA editor.
2. Insert a new module (Insert > Module).
3. Copy and paste the code into the module.
4. Modify « Sheet1 » and « DynamicDataRange » if needed.
5. Run CreateDynamicRange using F5 or assign it to a button.

Advantages of This Approach

Automatically updates the named range when new data is added.
Avoids using volatile functions like OFFSET in defined names.
Provides a clear, maintainable approach for working with dynamic data.
Works well in formulas, charts, and PivotTables.