Create Dynamic Range Reporting with Excel VBA

Step-by-Step Guide:

Step 1: Set up your Excel workbook

First, create an Excel workbook with some data that will serve as the source for your dynamic range. For example, let’s assume we have a table in Sheet1 that contains sales data, with columns like:

• A: Date
• B: Product
• C: Quantity
• D: Price
• E: Total (calculated as Quantity * Price)

You will be dynamically selecting a range of data based on certain conditions (e.g., dates or quantities).

Step 2: Open Visual Basic For Applications (VBA) Editor

To start writing the VBA code, press Alt + F11 to open the VBA editor.

Step 3: Insert a New Module

Once in the editor, go to the menu and click Insert > Module. This will insert a new module where you can write your code.

Step 4: Write VBA Code for Dynamic Range Reporting

Here’s the VBA code that dynamically selects a range based on the data, then generates a report (for example, summing totals or generating specific insights from the selected range):

Sub CreateDynamicReport()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim startDate As Date
    Dim endDate As Date
    Dim reportRange As Range
    Dim totalSales As Double
    Dim totalQuantity As Double
    Dim i As Long   
    ' Define the worksheet containing your data
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last row of data (assuming column A has no blanks in the data range)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Set up the date range (you can customize these values)
    startDate = DateSerial(2025, 1, 1) ' Start date (e.g., January 1, 2025)
    endDate = DateSerial(2025, 12, 31) ' End date (e.g., December 31, 2025)   
    ' Loop through the data to find the rows within the date range
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value >= startDate And ws.Cells(i, 1).Value <= endDate Then
            ' Add the rows that fall within the date range to the report range
            If reportRange Is Nothing Then
                Set reportRange = ws.Rows(i)
            Else
                Set reportRange = Union(reportRange, ws.Rows(i))
            End If
        End If
    Next i   
    ' Check if any rows were found
    If Not reportRange Is Nothing Then
        ' Calculate total sales and total quantity for the selected range
        totalSales = 0
        totalQuantity = 0
        For Each cell In reportRange.Columns(5).Cells ' Column E has Total values (Quantity * Price)
            totalSales = totalSales + cell.Value
        Next cell       
        For Each cell In reportRange.Columns(3).Cells ' Column C has Quantity values
            totalQuantity = totalQuantity + cell.Value
        Next cell       
        ' Output the report summary (you can customize this part)
        ws.Range("G1").Value = "Total Sales: " & totalSales
        ws.Range("G2").Value = "Total Quantity: " & totalQuantity       
        MsgBox "Report Generated Successfully!", vbInformation
    Else
        MsgBox "No data found for the given date range.", vbExclamation
    End If
End Sub

Step 5: Customize the Code

You can customize this code by:

• Modifying the range selection logic: If you want to base the dynamic range on other criteria (e.g., product, region, or sales amount), you can change the loop conditions to filter on different columns.
• Changing the report output: The report can be customized to show other summaries such as average sales, maximum sales, etc.
• Adjusting the date range: You can dynamically set the startDate and endDate values based on user input (e.g., using an input box or cell references).

Step 6: Run the Code

After writing your VBA code, you can run it by:

1. Pressing F5 while in the VBA editor, or
2. Going back to Excel, creating a button (from Insert > Shapes), and linking the button to this macro.

Once the code runs, it will:

1. Select the range of data that matches your criteria (in this case, the date range).
2. Generate the report with total sales and total quantity.
3. Display a message box confirming the report was generated.

Explanation of the Code

• Worksheet Setup: The code starts by defining the worksheet and finding the last row of data (lastRow), which is used to loop through all rows in the data range.
• Dynamic Range Selection: It then checks each row’s date (column A) to see if it falls within the specified start and end dates. If a row matches, it is added to the reportRange.
• Summing Totals: After identifying the relevant rows, the code sums the values in the total sales (column E) and quantity (column C) to give an overall summary of the selected data.
• Report Output: The results are displayed in columns G1 and G2, and a message box is shown confirming the report has been created.

Output

The code will display:

• The Total Sales and Total Quantity in cells G1 and G2.
• A Message Box indicating whether the report was generated or if no data was found for the given date range.

Conclusion

This VBA code helps you generate dynamic reports based on date ranges or other criteria. You can easily extend this by adding more complex logic for filtering, grouping, or aggregating your data.