Working with Excel Tables

An Excel Table — what VBA calls a ListObject — is a named, self-expanding range with built-in filter arrows, banded rows, and structured column references. If your data isn't already a Table, select any cell inside it and press Ctrl+T, or let VBA create one using ListObjects.Add.

Referencing a ListObject

Dim ws As Worksheet
Dim tbl As ListObject
 
Set ws = ThisWorkbook.Worksheets("Reports")
Set tbl = ws.ListObjects("tblReports")
 
Debug.Print tbl.Range.Address        ' full table including header
Debug.Print tbl.DataBodyRange.Rows.Count   ' data rows only, no header

Declaring tbl As ListObject (rather than just As Range) is what unlocks every Table-specific feature used for the rest of this section — ListRows, ListColumns, and the Total Row all come from the object being typed correctly. Notice the distinction between the two Debug.Print lines: tbl.Range covers the entire Table including its header row, while tbl.DataBodyRange covers only the data underneath it. '

Almost everything you do — adding a row, summing a column, looping through records — should use DataBodyRange, precisely because you don't want the header text accidentally getting treated as a data row.

Adding Rows

ListRows.Add appends a new row directly beneath the table — and critically, any structured-reference formulas in other columns extend into it automatically, which is one of the biggest practical advantages a Table has over a plain range.

Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
 
newRow.Range(1, 1).Value = "April"
newRow.Range(1, 2).Value = "North"
newRow.Range(1, 3).Value = 45200
newRow.Range(1, 4).Value = 30750
newRow.Range(1, 5).Value = 14450
newRow.Range(1, 6).Value = 41000

tbl.ListRows.Add creates the blank row and hands it back as a ListRow object, which is why the next six lines write to newRow rather than back to tbl. newRow.Range(1, 1) means "row 1 of this specific new row, column 1" — the indexing restarts at 1 for the new row itself, it isn't counting from the top of the whole table. This is a meaningfully better habit than finding the sheet's last row with End(xlUp) and writing one column past it by hand: ListRows.Add always lands correctly inside the Table's boundary, so any Total Row, structured-reference formula, or conditional formatting rule applied to the Table extends automatically to include it.

Updating Records

To update an existing row, loop through DataBodyRange and match on a key column — here, updating February's Central region Target after a budget revision:

Dim r As Long
For r = 1 To tbl.DataBodyRange.Rows.Count
    If tbl.DataBodyRange.Cells(r, 1).Value = "February" And _
       tbl.DataBodyRange.Cells(r, 2).Value = "Central" Then
        tbl.DataBodyRange.Cells(r, 6).Value = 52000   ' revised Target
        Exit For
    End If
Next r

This is the same top-to-bottom, stop-at-first-match pattern from conditional logic, applied to real rows instead of hardcoded values: the loop checks Month and Region together with And, and the moment both match, it updates the Target column and calls Exit For so it doesn't keep scanning the remaining rows needlessly. Using tbl.DataBodyRange.Cells(r, 1) rather than a worksheet-level Cells reference keeps the row numbering scoped to the Table's own data — row 1 here means the first data row, regardless of which physical worksheet row the Table happens to start on.

Referencing Table Columns

Structured references — ListColumns("Name") — are more readable and more resilient than counting columns by number, especially once a table gets edited and columns move:

Dim profitCol As Range
Set profitCol = tbl.ListColumns("Profit").DataBodyRange
 
Debug.Print Application.WorksheetFunction.Sum(profitCol)
Debug.Print Application.WorksheetFunction.Average(profitCol)

ListColumns("Profit") finds the column by its header text rather than by counting position, so the code kept working even if Profit later moved from column E to column F — counting Cells(r, 5) by hand would silently break in that scenario. Application.WorksheetFunction is the bridge that lets VBA call ordinary Excel functions like SUM and AVERAGE directly against a Range object, instead of you writing a manual loop with a running total, which is both less code and less likely to contain an off-by-one mistake.

Task

1. Open Section_4_Reports.xlsx, save it as Section_4_Reports.xlsm, and confirm the Reports sheet's data is a Table named tblReports (click any cell inside it — the Table Design tab should appear).
2. Write a macro that adds an April row for every one of the five regions using ListRows.Add (five new rows total, invented figures are fine).
3. Write a second macro using ListColumns("Sales").DataBodyRange and WorksheetFunction.Sum to print total Sales across all rows to the Immediate Window.