Create Dynamic Range Deleting with Excel VBA

VBA Code for Creating and Deleting a Dynamic Range:

Sub CreateAndDeleteDynamicRange()
    Dim ws As Worksheet
    Dim dynamicRange As Range
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim rangeName As String
    ' Set the worksheet you are working with
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row and column with data in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Last row in column A
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ' Last column in row 1
    ' Define the dynamic range
    Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))
    ' Name the dynamic range (optional)
    rangeName = "DynamicRange"
    On Error Resume Next ' In case the range name already exists
    ws.Names(rangeName).Delete ' Delete existing named range
    On Error GoTo 0 ' Turn back on regular error handling
    ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange
    ' Display a message showing the range is created
    MsgBox "Dynamic range '" & rangeName & "' has been created from A1 to " & _
        ws.Cells(lastRow, lastColumn).Address
    ' Now, delete the dynamic range
    ws.Names(rangeName).Delete
    MsgBox "Dynamic range '" & rangeName & "' has been deleted."
End Sub

Explanation of the Code:

1. Setting the Worksheet (ws):

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

This line sets the worksheet object (ws) to reference the sheet named « Sheet1. » You can change this to any sheet name in your workbook.

2. Finding the Last Row and Column:

lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row

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

• • lastRow: This finds the last row with data in column A. It does this by counting from the bottom of the worksheet (ws.Rows.Count) and using xlUp to move upwards until it hits a non-empty cell.
  • lastColumn: Similarly, this finds the last column with data by starting from the farthest column in row 1 and moving to the left using xlToLeft.

3. Defining the Dynamic Range:

Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))

The dynamicRange is defined from cell A1 to the cell determined by lastRow and lastColumn. This creates a flexible range that expands or contracts based on the data.

4. Naming the Dynamic Range:

rangeName = « DynamicRange »

• On Error Resume Next
• Names(rangeName).Delete
• On Error GoTo 0
• Names.Add Name:=rangeName, RefersTo:=dynamicRange

• • The range is given a name (« DynamicRange ») so it can be easily referenced later in Excel formulas or other parts of the code.
  • The code first checks if the named range already exists (On Error Resume Next temporarily disables error handling). If it exists, it deletes the old range (ws.Names(rangeName).Delete).
  • Then, it creates a new named range (ws.Names.Add Name:=rangeName, RefersTo:=dynamicRange).

5. Deleting the Dynamic Range:

• Names(rangeName).Delete

Finally, the code deletes the dynamic range that was previously named. The MsgBox provides feedback to the user that the range has been deleted.

How It Works:

• The code first calculates the size of the data set based on the last row and column with data.
• Then, it defines a dynamic range that spans from A1 to the last used cell.
• The range is given a name to make it easier to reference in formulas or other parts of the workbook.
• After performing actions with the range, the named range is deleted to clean up.

Use Case:

This VBA script is useful in scenarios where:

• You have a dataset that may change in size, and you need to create a range that dynamically adjusts.
• You want to create a named dynamic range for use in Excel formulas and then delete it when no longer needed.