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.Cellsrather than bareCellswhen the active sheet could differ from the intended sheet. - Empty sheets: Handle the case where a search returns
Nothingor a header row contains no entries before using a calculated boundary. - Variable types: Declare a numeric counter as
Longand a column object asRange. 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
- Excel Text to Columns: How to Enter a Delimiter
- Excel Text to Columns: Can It Split Capital Letters?
- Excel Text to Columns: Split a Cell at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- String in VBA: Meaning, Types, and Practical ExamplesIn VBA, String is the data type used to store text, such as names, messages, codes, and text…
- Excel VBA: Open a SharePoint File in Edit ModeYes. In Excel for Microsoft 365 on Windows, VBA can ask Excel to open a SharePoint workbook with…
- VBA AutoFilter Operator: Values, Uses, and ExamplesIn VBA, the Operator argument of Range.AutoFilter tells Excel how to combine two criteria for one field or…
- VBA Copy and Paste Data Between Excel SheetsTo copy a range from one Excel worksheet to another with VBA, use a macro that names both…
- Excel VBA Filter Multiple Criteria: Examples and CodeTo filter by multiple criteria in Excel VBA, use the range’s AutoFilter method: apply one criterion per field…
- VBA Contains String Function? How to Find Text with InStrYes—VBA can check whether one string contains another, but it does not have a built-in string method named…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.