Create Dynamic Range Troubleshooting with Excel VBA

VBA Code for Creating a Dynamic Range & Troubleshooting Issues

This code dynamically defines a range based on the last row and column in a dataset and includes error handling for debugging issues.

Sub CreateDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim rng As Range
    Dim rngAddress As String
    ' Set the worksheet
    On Error Resume Next ' Handle potential errors in case the sheet does not exist
    Set ws = ActiveSheet
    On Error GoTo 0 ' Re-enable error reporting
    If ws Is Nothing Then
        MsgBox "Error: No active worksheet found!", vbCritical, "Worksheet Error"
        Exit Sub
    End If   
    ' Find the last row with data in column A
    On Error Resume Next
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    On Error GoTo 0   
    If lastRow < 1 Then
        MsgBox "Error: No data found in column A!", vbExclamation, "Data Error"
        Exit Sub
    End If   
    ' Find the last column with data in row 1
    On Error Resume Next
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    On Error GoTo 0   
    If lastCol < 1 Then
        MsgBox "Error: No data found in row 1!", vbExclamation, "Data Error"
        Exit Sub
    End If   
    ' Define the dynamic range
    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))   
    ' Validate the range
    If rng Is Nothing Then
        MsgBox "Error: Unable to define the dynamic range!", vbCritical, "Range Error"
        Exit Sub
    End If   
    ' Store range address for reference
    rngAddress = rng.Address
    MsgBox "Dynamic Range successfully created: " & rngAddress, vbInformation, "Success"   
    ' Highlight the range
    rng.Interior.Color = RGB(200, 200, 255) ' Light Blue for visibility   
    ' Optional: Assign range to a named range
    On Error Resume Next
    ws.Names.Add Name:="DynamicRange", RefersTo:=rng
    On Error GoTo 0  
    MsgBox "Named range 'DynamicRange' has been created!", vbInformation, "Named Range Created"
End Sub

Explanation of the Code

1. Initializing the Worksheet

• The code first attempts to set ws as the active worksheet.
• It uses On Error Resume Next to prevent crashes if no sheet is active.
• If ws is Nothing, it alerts the user and exits.

2. Finding the Last Used Row and Column

• lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
  • This finds the last non-empty cell in column A.
• lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
  • This finds the last non-empty cell in row 1.
• If either value is less than 1, an error message is shown.

3. Defining the Dynamic Range

• The code constructs the range from (1,1) (A1) to (lastRow, lastCol).
• If the range is Nothing, an error message is displayed.

4. Validating and Highlighting the Range

• The valid range is highlighted in light blue for visibility.
• The range address is displayed in a message box.

5. Creating a Named Range for Future Use

• The code assigns the dynamic range to a named range « DynamicRange ».
• This can be used in formulas or further automation.

Troubleshooting Errors