Why Tables Beat Plain Ranges

The question that eats half your macro

Most VBA written against a plain range exists to answer one question: where does the data stop? You write Cells(Rows.Count, "A").End(xlUp).Row, you quietly assume column A is never empty, and one morning the macro processes 40 rows out of 900 and reports a total that is wrong by five figures. Nothing errors. Nobody notices for a week. An Excel Table removes the question instead of answering it better.

The difference in five lines

' Plain range - a guess dressed up as code
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set dataRange = ws.Range("A2:F" & lastRow)

' Table - the object states its own extent
Set dataRange = ws.ListObjects("tblSales").DataBodyRange

In the object model an Excel Table is called a ListObject - Microsoft's older internal name for it, which is why the ribbon says Table and the code says ListObject.

Run this and watch the two answers disagree

Paste the whole thing into a standard module (Alt+F11, then Insert - Module) and press F5. It builds its own sheet and its own data, so nothing needs preparing. Two late entries deliberately have no order number - exactly what real exports look like.

Option Explicit

Sub BuildSalesTableDemo()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim rowsData As Variant
    Dim parts As Variant
    Dim r As Long
    Dim lastWritten As Long
    Dim guessedLastRow As Long
    Dim guessedTotal As Double
    Dim tableTotal As Double
    Dim msg As String

    rowsData = Array( _
        "Order|Region|Product|Amount", _
        "A-1001|Zurich|Keyboard|89.90", _
        "A-1002|Bern|Monitor|249.00", _
        "A-1003|Zurich|Mouse|29.50", _
        "A-1004|Basel|Keyboard|89.90", _
        "A-1005|Bern|Docking station|179.00", _
        "A-1006|Zurich|Monitor|249.00", _
        "A-1007|Basel|Mouse|29.50", _
        "A-1008|Bern|Keyboard|89.90", _
        "|Zurich|Monitor|249.00", _
        "|Basel|Docking station|179.00")

    Application.ScreenUpdating = False

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("TableDemo").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set ws = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = "TableDemo"

    For r = LBound(rowsData) To UBound(rowsData)
        parts = Split(rowsData(r), "|")
        ws.Cells(r + 1, 1).Value = parts(0)
        ws.Cells(r + 1, 2).Value = parts(1)
        ws.Cells(r + 1, 3).Value = parts(2)
        If r = LBound(rowsData) Then
            ws.Cells(r + 1, 4).Value = parts(3)
        Else
            ws.Cells(r + 1, 4).Value = Val(parts(3))
        End If
    Next r

    lastWritten = UBound(rowsData) - LBound(rowsData) + 1

    Set lo = ws.ListObjects.Add( _
        SourceType:=xlSrcRange, _
        Source:=ws.Range("A1:D" & lastWritten), _
        XlListObjectHasHeaders:=xlYes)

    lo.Name = "tblSalesDemo"
    lo.TableStyle = "TableStyleMedium2"
    ws.Columns("A:D").AutoFit

    guessedLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    guessedTotal = Application.Sum(ws.Range("D2:D" & guessedLastRow))
    tableTotal = Application.Sum(lo.ListColumns("Amount").DataBodyRange)

    Application.ScreenUpdating = True

    msg = "Rows the last-row trick finds: " & (guessedLastRow - 1) & vbCrLf & _
          "Rows the table knows about:    " & lo.ListRows.Count & vbCrLf & vbCrLf & _
          "Total over the guessed range: " & Format$(guessedTotal, "#,##0.00") & vbCrLf & _
          "Total over the table column:  " & Format$(tableTotal, "#,##0.00")

    Debug.Print msg
    MsgBox msg, vbInformation, "Same sheet, two different answers"
End Sub

Reading it line by line

• rowsData = Array(...) holds the sample rows as pipe-separated text; Split cuts each line into four fields.
• Val(parts(3)) converts the amount. Val always reads . as the decimal point, so the numbers come out the same on a German or French system, where CDbl would fail.
• The On Error Resume Next block around .Delete makes the macro re-runnable: an existing sheet is removed, and the missing-sheet error is ignored if there is none.
• ws.ListObjects.Add turns the block into a table. xlYes says row 1 holds the headers - get it wrong and your columns are called Column1 to Column4.
• lo.Name replaces the automatic Table1 with an address that survives inserted rows and renamed sheets.
• lo.ListRows.Count and lo.ListColumns("Amount").DataBodyRange ask the table what it holds. No search, no assumption, no arithmetic.

The trap: End(xlUp) only ever looks at one column

The message box puts the two answers side by side: the trick finds 8 rows, the table reports 10, and the totals differ by 428.00. End(xlUp) walks up column A and stops at the first cell with anything in it, so the two rows with no order number are invisible to it - although their amounts are perfectly real. This is the most expensive bug in beginner VBA, because it returns a plausible number instead of an error. Change "A" to "D" in that line and the guess suddenly agrees, which is exactly the problem: the answer depends on the column you happened to pick.

What else a table hands you at no cost

• It grows by itself. Type in the row underneath and the table takes it in, with formulas, formatting and filters.
• Its parts are separate objects - header row, data body and totals row, none of them counted by hand.
• Formulas read like sentences: =[@Amount]*0.081 instead of =D2*0.081.
• Charts and PivotTables built on it follow it as it grows, with no code at all.

When a table is the wrong choice

Tables hold no merged cells and no stacked header rows - no loss, since neither belongs in source data. They are also wrong for a finished report with subtotals and spacer rows. Keep the raw list in a table and build the report from it.

Getting hold of it from anywhere

Dim lo As ListObject

' A table name is unique in the whole workbook, so this finds it
' no matter which sheet somebody dragged it onto
Set lo = ThisWorkbook.Worksheets("TableDemo").ListObjects("tblSalesDemo")

Debug.Print lo.ListRows.Count; " data rows"; " in "; lo.Range.Address

How it goes on

The next lesson maps the model properly - Range, HeaderRowRange, DataBodyRange - including the case that breaks the macro above: an empty table, where DataBodyRange is Nothing. After that come converting existing ranges, adding and deleting rows, structured references, reading a table into an array in one statement, and filtering and sorting from code.