Create Dynamic Range Training with Excel VBA

VBA Code: Create Dynamic Range in Excel

This VBA code dynamically defines a named range based on the number of filled rows in a specific column.

Sub CreateDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rngName As String
    Dim colLetter As String
    Dim dynamicRange As Range   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your actual sheet name   
    ' Define the column to check for the last row
    colLetter = "A" ' Change to the column where your dynamic data is located   
    ' Find the last non-empty row in the specified column
    lastRow = ws.Cells(ws.Rows.Count, colLetter).End(xlUp).Row   
    ' Define the dynamic range
    Set dynamicRange = ws.Range(ws.Cells(2, colLetter), ws.Cells(lastRow, colLetter)) ' Starts from row 2   
    ' Set the name of the range
    rngName = "DynamicData"   
    ' Delete the named range if it already exists
    On Error Resume Next
    ThisWorkbook.Names(rngName).Delete
    On Error GoTo 0   
    ' Create a new named range
    ThisWorkbook.Names.Add Name:=rngName, RefersTo:=dynamicRange   
    ' Notify user
    MsgBox "Dynamic range '" & rngName & "' has been created successfully!", vbInformation, "Success"  
End Sub

Detailed Explanation of the VBA Code

1. Declaring Variables

Dim ws As Worksheet

Dim lastRow As Long

Dim rngName As String

Dim colLetter As String

Dim dynamicRange As Range

• ws: Represents the worksheet where the dynamic range will be created.
• lastRow: Stores the last row number in the specified column.
• rngName: The name assigned to the dynamic range.
• colLetter: Stores the column letter where the dynamic range is created.
• dynamicRange: A range object that will hold the dynamic data.

2. Setting the Worksheet

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

• This assigns ws to a specific sheet. Change « Sheet1 » to match the sheet where your data is located.

3. Finding the Last Row with Data

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

• ws.Rows.Count: Gets the total number of rows in the sheet (1,048,576 in Excel 2007+).
• .End(xlUp): Works like pressing Ctrl + Up Arrow, moving from the last row up to the first filled cell.
• .Row: Extracts the row number of the last filled cell.

4. Defining the Dynamic Range

Set dynamicRange = ws.Range(ws.Cells(2, colLetter), ws.Cells(lastRow, colLetter))

• The range starts from row 2 (assuming row 1 is a header).
• The range extends down to lastRow (the last filled row in column « A »).

5. Deleting the Previous Named Range

On Error Resume Next

ThisWorkbook.Names(rngName).Delete

On Error GoTo 0

• On Error Resume Next prevents errors if the named range does not exist.
• ThisWorkbook.Names(rngName).Delete removes any previous named range with the same name.
• On Error GoTo 0 resets error handling.

6. Creating the Named Range

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

• The Names.Add method assigns the range stored in dynamicRange to the named range « DynamicData ».

7. Displaying a Confirmation Message

MsgBox « Dynamic range ‘ » & rngName & « ‘ has been created successfully! », vbInformation, « Success »

• This informs the user that the dynamic range was created successfully.

How to Use This Code

1. Open your Excel workbook.
2. Press ALT + F11 to open the VBA Editor.
3. Click Insert > Module to create a new module.
4. Copy and paste the code into the module.
5. Run CreateDynamicRange by pressing F5 or running it manually.

Dynamic Named Range Benefits

• Automatically adjusts when data changes.
• Useful for charts, dropdown lists, and PivotTables.
• Ensures flexibility in reports and dashboards.