Create Dynamic Range Upgradation with Excel VBA

Concept: Dynamic Range Upgradation

In Excel VBA, a dynamic range refers to a range that adjusts automatically when new data is added or removed. This is useful in dashboards, reports, and pivot tables.

VBA Code for Creating a Dynamic Range

This VBA macro:

1. Identifies the last row and column in a dataset.
2. Defines a named range dynamically using this last row and column.
3. Updates the named range when the dataset changes.

Here is the detailed code:

Sub UpdateDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim dataRange As Range
    Dim sheetName As String
    Dim rangeName As String
    ' Define the worksheet and named range
    sheetName = "Sheet1" ' Change this to your sheet name
    rangeName = "DynamicRange" ' Name of the dynamic range   
    ' Set the worksheet reference
    Set ws = ThisWorkbook.Sheets(sheetName)   
    ' Find the last used row in column A (adjust for your dataset)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row   
    ' Find the last used column in row 1
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   
    ' Define the dynamic range
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))   
    ' Apply the named range
    ws.Names.Add Name:=rangeName, RefersTo:=dataRange   
    ' Inform the user
    MsgBox "Dynamic Range '" & rangeName & "' updated to: " & _
           dataRange.Address, vbInformation, "Update Successful"   
End Sub

Detailed Explanation

1. Identifying the Worksheet and Named Range

• The macro starts by defining the worksheet (Sheet1) and the named range (DynamicRange).
• These can be modified as per your requirement.

2. Finding the Last Used Row and Column

• The last row is determined using:

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

• • This searches Column A (first column) from the bottom and stops at the last non-empty row.
• The last column is found using:

• lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• • This scans Row 1 from the rightmost column to find the last non-empty column.

3. Defining the Dynamic Range

• The range is created using:

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

• • This captures all non-empty cells starting from A1 to the last detected row and column.

4. Creating or Updating the Named Range

• The line:

• Names.Add Name:=rangeName, RefersTo:=dataRange

• • Creates or updates the named range « DynamicRange » to refer to the new dynamic area.

5. Displaying a Confirmation Message

• A message box appears after execution:

• MsgBox « Dynamic Range ‘ » & rangeName & « ‘ updated to:  » & _
• Address, vbInformation, « Update Successful »

• • This informs the user of the updated range.

Use Case

• This macro is useful for updating charts, pivot tables, or data validation dynamically.
• Instead of manually updating the named range when new data is added, running this macro ensures the range is always up-to-date.

Enhancements

To automate the range update whenever data is changed, we can use the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.UsedRange) Is Nothing Then

        Call UpdateDynamicRange

    End If

End Sub

• This ensures the macro runs automatically when any data is changed in the worksheet.