Create Dynamic Range Testing with Excel VBA

Objective

The goal is to create a VBA macro that defines a dynamic range in an Excel worksheet, updates it based on the data present, and tests if it works correctly.

VBA Code: Creating and Testing a Dynamic Range

Let’s start with a fully detailed code.

Option Explicit
Sub CreateAndTestDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim dynamicRange As Range
    Dim testCell As Range   
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last used row in column A (assuming data starts in A1)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row   
    ' Find the last used column in row 1 (assuming headers start in A1)
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   
    ' Define the dynamic range
    Set dynamicRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))   
    ' Name the dynamic range (optional)
    ws.Names.Add Name:="DynamicRange", RefersTo:=dynamicRange   
    ' Output message to confirm
    MsgBox "Dynamic Range has been set from " & dynamicRange.Address, vbInformation, "Range Defined"   
    ' --- TESTING THE DYNAMIC RANGE ---   
    ' Loop through the range to check its values
    For Each testCell In dynamicRange
        ' Highlight empty cells to check errors
        If IsEmpty(testCell) Then
            testCell.Interior.Color = RGB(255, 200, 200) ' Light Red for Empty Cells
        Else
            testCell.Interior.Color = RGB(200, 255, 200) ' Light Green for Filled Cells
        End If
    Next testCell 
    MsgBox "Dynamic range tested! Empty cells highlighted in red.", vbInformation, "Testing Complete"
End Sub

Detailed Explanation of the Code

1. Option Explicit
   • This ensures that all variables must be explicitly declared, preventing errors due to typos.
2. Defining Variables
   • ws: Holds the reference to the worksheet where the dynamic range is created.
   • lastRow: Finds the last used row in column A.
   • lastCol: Finds the last used column in row 1.
   • dynamicRange: Stores the dynamic range of data.
   • testCell: Used in the loop to test and highlight empty cells.
3. Setting the Worksheet
   • Set ws = ThisWorkbook.Sheets(« Sheet1 »)
   • Assigns the worksheet Sheet1 from the active workbook.
4. Finding the Last Used Row

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

• • This starts from the last row of column A and moves up to find the last non-empty cell.

5. Finding the Last Used Column

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

• • This starts from the last column of row 1 and moves left to find the last non-empty cell.

6. Defining the Dynamic Range

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

• • Creates a range from A1 to the last detected row and column.

7. Naming the Dynamic Range (Optional)

• Names.Add Name:= »DynamicRange », RefersTo:=dynamicRange

• • Creates a named range called DynamicRange.

8. Displaying Confirmation Message

• MsgBox « Dynamic Range has been set from  » & dynamicRange.Address, vbInformation, « Range Defined »

• • Shows the user a message confirming the defined range.

9. Testing the Dynamic Range

• For Each testCell In dynamicRange

• • Loops through each cell in the dynamic range.

10. Highlighting Empty and Filled Cells

• If IsEmpty(testCell) Then

Interior.Color = RGB(255, 200, 200) ‘ Light Red for Empty Cells

• Else

Interior.Color = RGB(200, 255, 200) ‘ Light Green for Filled Cells

• End If

• • Red (255, 200, 200): Empty cells.
  • Green (200, 255, 200): Filled cells.

11. Final Confirmation Message

• MsgBox « Dynamic range tested! Empty cells highlighted in red. », vbInformation, « Testing Complete »

1. • Notifies the user that the testing is complete.

How to Use This Code

1. Open an Excel workbook.
2. Press ALT + F11 to open the VBA Editor.
3. Go to Insert > Module.
4. Paste the VBA code inside the module.
5. Modify Sheet1 if needed (change the sheet name).
6. Run CreateAndTestDynamicRange from the macro list.

Key Benefits of This Approach

Fully Automated: Detects the data range dynamically.
Easy to Modify: You can adjust the range criteria as needed.
Visual Feedback: Highlights empty cells for validation.
Error-Free: Uses Option Explicit and MsgBox for confirmation.