Excel VBA Loop Through Columns: Practical Examples

VBA & macros · Updated November 21, 2026

To loop through columns in Excel VBA, use a For loop with a column number, or a For Each loop over a range’s Columns collection. A numbered loop is a good fit for fixed or calculated bounds; For Each is convenient when the columns to visit are already defined as a range.

How a column loop works

VBA represents a worksheet column as a range, and the Cells(row, column) property lets a macro address a cell by its numeric row and column indexes. Column A is 1, B is 2, and so on. A loop changes the column index each time around, while the row index can stay fixed or be handled by a second, nested loop.

This short example prints the values in the headers from columns B through F on a worksheet named Data:

Sub ListColumnHeaders()
    Dim ws As Worksheet
    Dim col As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Visit columns B through F.
    For col = 2 To 6
        Debug.Print col, ws.Cells(1, col).Value
    Next col
End Sub

The first argument to Cells is the row, so Cells(1, col) reads row 1 as col changes. The macro writes to the Visual Basic Editor’s Immediate window, not to the worksheet. In the editor, press Ctrl+G on Windows to show that window. On Mac, the Visual Basic Editor and its available shortcuts can vary with the keyboard; the VBA code itself uses the same basic syntax.

Choose between For and For Each

Use a numbered For loop when the start and end columns are numeric, when you need to move backward, or when the column number is part of your calculation. For example, For col = 2 To 6 clearly identifies a five-column range. You can also set the end value from a variable, which is useful when the amount of data changes from one run to another.

Use For Each when you have a range of columns and want VBA to supply each column range in turn. This example visits B through F and prints each column’s header:

Sub ListHeadersWithForEach()
    Dim ws As Worksheet
    Dim colRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Each item in this collection is one column range.
    For Each colRange In ws.Range("B:F").Columns
        Debug.Print colRange.Cells(1, 1).Address(False, False), _
                    colRange.Cells(1, 1).Value
    Next colRange
End Sub

The variable in a For Each loop should be an object variable such as Range, not a Long. A numbered loop instead uses a numeric counter such as Long. Both patterns are valid; choose the one that makes the bounds and purpose easiest to understand.

Find the last column to process

For a fixed layout, put the first and last column numbers directly in the loop. For a changing table, calculate the last column first. If row 1 contains a header in every populated column, this expression finds the last non-empty cell in that row by searching from the worksheet’s far-right column toward the left:

Sub CountColumnsWithHeaders()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim col As Long
    Dim nonblank As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Stop if the header row has no entries.
    If Application.CountA(ws.Rows(1)) = 0 Then Exit Sub

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

    ' Count non-empty cells in each column through the last header.
    For col = 1 To lastCol
        nonblank = Application.CountA(ws.Columns(col))
        Debug.Print ws.Cells(1, col).Value, nonblank
    Next col
End Sub

This example assumes the headers are in row 1 and that every column you want to include has a header. If the sheet contains a note or stray entry far to the right in row 1, the loop will include those columns too. Conversely, a column with data but no header can be missed if it lies beyond the last header. Choose a row that reliably marks the table’s width, or define the range explicitly when the layout is known.

When data may appear in any row, find the last used cell across the whole sheet instead. The following approach checks for a cell containing a value or formula and handles a completely empty sheet:

Sub FindLastUsedColumn()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim lastCol As Long

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search by column order, starting at the end.
    Set lastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)

    If lastCell Is Nothing Then
        Debug.Print "The worksheet is empty."
    Else
        lastCol = lastCell.Column
        Debug.Print "Last used column number:", lastCol
    End If
End Sub

Specifying the search options matters because Excel’s Find settings can be reused from earlier searches. Searching with LookIn:=xlFormulas includes cells containing formulas, even if a formula displays an empty string. If your definition of “used” should exclude formulas or should follow a particular data row, adjust the search method to match that rule.

Loop through cells inside each column

A column loop often serves as the outer loop in a two-dimensional task. For example, to inspect data rows 2 through 100 in each column, put a row loop inside the column loop. This version prints only numeric values and does not change the worksheet:

Sub PrintNumbersByColumn()
    Dim ws As Worksheet
    Dim col As Long
    Dim rowNum As Long
    Dim lastCol As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastCol = 5

    ' Check columns B through the chosen final column.
    For col = 2 To lastCol
        For rowNum = 2 To 100
            If IsNumeric(ws.Cells(rowNum, col).Value) Then
                Debug.Print ws.Cells(1, col).Value, _
                            ws.Cells(rowNum, col).Value
            End If
        Next rowNum
    Next col
End Sub

Change the row bounds to fit the actual table. If the number of rows varies, calculate a last row for each column or for a reliable key column rather than assuming that row 100 is always the end. Also decide how to treat blank cells, text that looks like a number, error values, and formulas that return empty text. Those details affect whether a test such as IsNumeric matches the task you intend to perform.

Use worksheet and workbook references deliberately

A common source of mistakes is relying on whichever worksheet happens to be active. Qualify ranges with a worksheet variable, as in ws.Cells(1, col), so the macro consistently reads the intended sheet. ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook means the workbook currently active in Excel, which might be a different file. Pick the reference that matches where the data is located.

For a table on another worksheet, set the reference explicitly, for example Set ws = ThisWorkbook.Worksheets("Sales"). The name must match an existing worksheet tab. If a sheet is renamed, a hard-coded name can cause a run-time error; for workbooks that rely on fixed sheets, consider whether a worksheet’s VBA code name is a better fit. Avoid selecting or activating a sheet just to read its cells: direct references are clearer and reduce unintended dependence on the user’s current selection.

Common problems and safer patterns

Check these points when a loop skips columns, stops too soon, or produces unexpected output:

  • Wrong starting index: If the table begins in column C, start at 3, not 2. Column numbers are not the same as letters.
  • Incorrect last-column rule: A last column derived from row 1 only reflects row 1. Confirm that the chosen header or key row represents the whole dataset.
  • Unqualified references: Use ws.Cells rather than bare Cells when the active sheet could differ from the intended sheet.
  • Empty sheets: Handle the case where a search returns Nothing or a header row contains no entries before using a calculated boundary.
  • Variable types: Declare a numeric counter as Long and a column object as Range. Undeclared or mismatched variables make errors harder to spot.
  • Unexpected formulas: A cell that looks blank may still contain a formula. Decide whether your test should consider formulas, displayed values, or truly empty cells.

Use Option Explicit at the top of a module to require variable declarations. It helps catch spelling mistakes in variable names before they become confusing results. During development, printing the column number, header, or address to the Immediate window can also show exactly which columns the loop visits before you add any actions that alter data.

When to loop backward and how to keep macros safe

If a macro deletes entire columns while looping, a forward loop can skip a column: after one column is removed, the next column shifts into its position, but the counter advances. In that case, loop from the highest column number down to the lowest. For example, a numeric loop can use For col = lastCol To firstCol Step -1. Deleting columns changes the worksheet structure, so confirm the selected range carefully before running such code.

For many read-only checks, looping through cells is straightforward. For very large datasets, repeated worksheet reads and writes can be slow. A common faster approach is to read a range into a VBA array, process the array in memory, and write results back in a block. That pattern needs more care around array bounds and data types, so a simple column loop is often preferable until performance is a real problem.

Save a separate copy of the workbook before running any macro that changes cell contents, formatting, or worksheet structure. VBA actions generally cannot be reversed with Ctrl+Z, and a macro can affect many cells quickly. Excel for the web can open a workbook containing VBA but does not run VBA macros; use desktop Excel for Microsoft 365 on Windows or Mac to execute them. On Windows, the Visual Basic Editor is commonly opened with Alt+F11; on Mac, Option+F11 is the usual equivalent, sometimes with the Fn key depending on keyboard settings.

Frequently Asked Questions

How do I loop through every column in a worksheet?
Use a numeric counter from 1 through ws.Columns.Count if you truly need to visit every worksheet column. Most tasks should instead calculate the last relevant column or use a specific range, because a worksheet has many columns and most may be empty.

How do I loop through columns A to Z?
Use For col = 1 To 26 with a declared Long variable, then refer to each column with ws.Cells(rowNum, col) or ws.Columns(col). This works because A is column 1 and Z is column 26.

Is For Each better than For for columns?
Neither is universally better. For Each is concise when iterating over a known range such as ws.Range("B:F").Columns. A numbered For loop is more flexible when you need calculated bounds, a specific numeric index, or reverse traversal.

How can I loop through only columns that contain data?
Find the last relevant column using a row that has a dependable header in every data column, or use Range.Find to locate the last cell across the worksheet. The right choice depends on whether “contains data” means a header exists, a particular row is populated, or any cell in the column is used.

Can VBA loop through columns in Excel for the web?
The code can be stored in a macro-enabled workbook, but Excel for the web does not execute VBA macros. Open the workbook in the desktop version of Excel to run VBA; workbook editing in the browser does not substitute for running the macro.

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.