Excel VBA Loop Through Table Column: How-To

VBA & macros · Updated November 22, 2026

To loop through a table column in Excel VBA, use a For Each loop over the ListColumn.DataBodyRange or ListColumn.Range of a ListObject, then read each cell’s value. For example, For Each cell In tbl.ListColumns("Name").DataBodyRange iterates only the data cells, skipping the header.

What Is a Table in Excel VBA?

In Excel, a “table” (created with Ctrl+T or Insert > Table) is a structured range managed as a ListObject. Unlike a plain range, a table has a header row, automatic filtering, and structured references like Table1[Column1]. In VBA, you access tables through the ListObjects collection of a worksheet. Each ListObject has ListColumns (the columns) and ListRows (the rows). Understanding this object model is key because you cannot loop through a table column as a simple range—you must reference its parts.

How to Reference a Table Column in VBA

Before looping, you need to get a reference to the specific column. The ListObject is found via the worksheet’s ListObjects collection, usually by name (e.g., “Table1”). Then you access a column by its name or index. The column’s data cells (excluding header and totals) are in DataBodyRange. The entire column including header is Range.

Example: To reference the column named “Amount” in a table called “SalesTable”:

Dim tbl As ListObject
Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("SalesTable")
Dim col As ListColumn
Set col = tbl.ListColumns("Amount")
' Now col.DataBodyRange is the range of data cells in that column.

If you don’t know the column name, you can use the index: tbl.ListColumns(1) for the first column. But using names is safer because tables often get reordered.

Basic Loop Through a Table Column

The most straightforward way to loop through each cell in a table column is to iterate over the cells in DataBodyRange. Here’s a complete macro that sums all values in a numeric column:

Sub SumTableColumn()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("SalesTable")
    Dim cell As Range
    Dim total As Double
    total = 0
    ' Loop through each cell in the data body of the "Amount" column
    For Each cell In tbl.ListColumns("Amount").DataBodyRange
        If IsNumeric(cell.Value) Then
            total = total + cell.Value
        End If
    Next cell
    MsgBox "Total: " & total
End Sub

This works even if the table has filters applied—DataBodyRange includes all rows, not just visible ones. If you want to loop only visible rows, you need a different approach (see below).

Looping Through Visible or Filtered Rows

When a table is filtered, DataBodyRange still includes hidden rows. To loop only through visible cells, use the SpecialCells(xlCellTypeVisible) method on the column range. However, this can be tricky if all rows are hidden—it may return nothing. A safer method is to loop through the rows and check the Hidden property of the row.

Example that processes only visible rows:

Sub LoopVisibleRows()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("SalesTable")
    Dim rng As Range
    Dim cell As Range
    ' Get the data body range of the column
    Set rng = tbl.ListColumns("Amount").DataBodyRange
    ' Loop through each cell, skipping hidden rows
    For Each cell In rng
        If cell.EntireRow.Hidden = False Then
            ' Process visible cell
            Debug.Print cell.Value
        End If
    Next cell
End Sub

Alternatively, you can use rng.SpecialCells(xlCellTypeVisible) but be aware that if no cells are visible, it returns Nothing. Always test for Nothing before looping.

Looping by Row Index vs. Cell by Cell

Sometimes you need the row number or you want to compare values across columns. Instead of looping cell by cell, you can loop through the table’s ListRows collection. Each ListRow has a Range property that covers the entire row. This is useful when you need to read multiple columns in the same iteration.

Example that checks if the “Status” column says “Pending” and then updates the “Amount” column:

Sub LoopRowsAndUpdate()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("TasksTable")
    Dim rw As ListRow
    Dim statusCol As Long, amountCol As Long
    ' Get column indices by name
    statusCol = tbl.ListColumns("Status").Index
    amountCol = tbl.ListColumns("Amount").Index
    For Each rw In tbl.ListRows
        If rw.Range.Cells(1, statusCol).Value = "Pending" Then
            rw.Range.Cells(1, amountCol).Value = rw.Range.Cells(1, amountCol).Value * 1.1
        End If
    Next rw
End Sub

This method is cleaner when you need to work with multiple columns in the same row. Note that ListRows includes all rows, even if filtered; hidden rows are still in the collection.

Common Mistakes and How to Avoid Them

  • Forgetting to reference the correct worksheet: Always qualify the ListObjects with the worksheet, e.g., ThisWorkbook.Sheets("Data").ListObjects(1). Otherwise, VBA might use the active sheet, which can cause errors.
  • Using Range instead of DataBodyRange: If you loop over ListColumn.Range, you’ll include the header row. That can cause type mismatches if you expect numbers.
  • Assuming the table name is “Table1”: If you rename the table, the old name fails. Use ListObjects(1) or check the name in the Name Manager.
  • Modifying the table while looping: If you add or delete rows inside a For Each loop over ListRows, it can cause unexpected behavior. It’s safer to collect changes in an array and apply them after the loop.
  • Not handling empty cells: Always test for IsEmpty or IsNumeric before performing operations on cell values.

Performance Tips for Large Tables

Looping cell-by-cell in VBA can be slow for tables with thousands of rows. To speed up, you can read the entire column into a variant array, process it in memory, and write back. For example:

Sub FastLoop()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("SalesTable")
    Dim dataArr As Variant
    ' Read the whole column into an array (2D, 1-based)
    dataArr = tbl.ListColumns("Amount").DataBodyRange.Value
    Dim i As Long
    For i = LBound(dataArr, 1) To UBound(dataArr, 1)
        ' Process dataArr(i, 1)
        dataArr(i, 1) = dataArr(i, 1) * 2
    Next i
    ' Write back to the column
    tbl.ListColumns("Amount").DataBodyRange.Value = dataArr
End Sub

This is much faster because reading and writing a range as an array is a single operation. For very large data, consider using Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual during the loop, then restore them at the end.

Alternatives to VBA Loops

If you only need to perform simple operations, you might not need VBA at all. Excel’s built-in features can often do the job:

  • Formulas: Use structured references like =SUM(Table1[Amount]) to total a column.
  • Excel Tables’ Total Row: Enable the Total Row from Table Design > Total Row to get automatic SUM, AVERAGE, etc.
  • Power Query: For complex transformations, Power Query (Get & Transform) can handle data without code.
  • Array formulas: Use dynamic array functions like FILTER or UNIQUE (in Excel 365) to process columns without looping.

VBA is best when you need to automate a repetitive task, interact with other Office applications, or perform logic that formulas can’t handle.

Frequently Asked Questions

How do I loop through a table column and delete rows that meet a condition?
Loop backwards through the rows using For i = tbl.ListRows.Count To 1 Step -1, check the condition in the column, and delete tbl.ListRows(i).Range.Delete. Deleting forward is risky because row indices shift.

Can I loop through a table column without knowing the column name?
Yes, use the column index: tbl.ListColumns(2).DataBodyRange for the second column. But if the table structure changes, the index may point to a different column. Better to find the column by header text using Application.Match or a loop over tbl.HeaderRowRange.

Why does my loop skip rows when I delete inside a For Each?
Because For Each iterates over a collection that is re-evaluated when you delete an item, causing it to skip the next item. Always loop backwards by index when deleting.

How do I loop through only the visible cells after filtering?
Use Set rng = tbl.ListColumns("Amount").DataBodyRange.SpecialCells(xlCellTypeVisible), but check if rng is Nothing first. Alternatively, loop through all cells and check cell.EntireRow.Hidden.

Is there a way to loop through a table column without VBA?
Yes, you can use array formulas or dynamic arrays. For example, to double each value in a column, you can use =Table1[Amount]*2 in a helper column. For more complex logic, Power Query is a no-code alternative.

What does DataBodyRange return if the table has no data rows?
It returns Nothing or a range with zero rows. Always test with If Not tbl.ListColumns("Amount").DataBodyRange Is Nothing before looping to avoid errors.

Important Reminder Before Running Macros

Before running any VBA macro, save a copy of your workbook. Macros cannot be undone with Ctrl+Z, and if your code has an error, it might overwrite data. Always test on a backup or a sample sheet first.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.