Excel VBA: Loop Through All Rows in a Column

VBA & macros · Updated November 22, 2026

The simplest way to loop through all rows in a column with VBA is to use a For Each loop over the Range object that spans the used cells in that column, or a For loop from row 1 to the last used row. For example, For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) iterates through every non-empty cell in column A. This article explains the core techniques, how to choose between them, common pitfalls, and practical examples you can adapt.

Why Loop Through a Column in VBA?

Looping through a column is one of the most common tasks in Excel VBA because it lets you automate repetitive actions on data: cleaning text, applying calculations, copying values, or checking conditions. Instead of manually selecting each cell, a loop processes every row in a defined range, saving time and reducing errors. The key is to define the range correctly—either the entire column (which can be slow) or just the used rows (which is faster and more precise).

Basic Loop Structures: For Each vs. For

There are two main ways to loop through rows in a column:

  • For Each – Iterates over a collection of cells. It’s clean and readable, but you can’t easily skip rows or change the loop counter.
  • For – Uses an index variable (e.g., i = 1 to lastRow). It gives you full control over row numbers, which is useful when you need to reference the row in formulas or skip certain rows.

Here’s a basic For Each example that writes “Processed” in column B for each non-empty cell in column A:

Sub LoopColumnForEach()
    Dim cell As Range
    Dim lastRow As Long
    
    ' Find last used row in column A
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each cell in A1:A{lastRow}
    For Each cell In Range("A1:A" & lastRow)
        If cell.Value <> "" Then
            cell.Offset(0, 1).Value = "Processed"
        End If
    Next cell
End Sub

And the equivalent with a For loop:

Sub LoopColumnFor()
    Dim i As Long
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To lastRow
        If Cells(i, 1).Value <> "" Then
            Cells(i, 2).Value = "Processed"
        End If
    Next i
End Sub

Both produce the same result. The For loop is often preferred when you need to delete rows (because you can loop backwards) or when you need to compare values across rows.

How to Find the Last Used Row Correctly

Determining the last row is critical—if you guess too small, you miss data; too large, you waste time. The standard method is Cells(Rows.Count, 1).End(xlUp).Row, which works like pressing Ctrl+Up from the bottom of the sheet. However, it has a caveat: it stops at the first non-empty cell, but if there are gaps (e.g., row 5 is empty but row 6 has data), it will stop at row 4. To handle that, you can use UsedRange or CurrentRegion:

  • ActiveSheet.UsedRange.Rows.Count – counts all rows that have ever been used, including formatting, which can be larger than actual data.
  • Range("A1").CurrentRegion.Rows.Count – extends to contiguous data around A1, but only works if your data is a contiguous block.

For most cases, End(xlUp) is sufficient, but if your column has blank rows in the middle, consider using WorksheetFunction.CountA to get the count of non-empty cells, or loop from the bottom up using End(xlUp) on the entire column.

Looping Through All Rows in a Column (Including Empty Cells)

If you need to loop through every row in a column regardless of whether it’s empty—for example, to check for missing data—you can loop to the last row of the worksheet’s used range or to a fixed number like 1048576 (the maximum rows in Excel). But looping to 1,048,576 rows is extremely slow. A better approach is to loop to the last row of the used range, even if some cells are blank:

Sub LoopAllRowsInColumn()
    Dim i As Long
    Dim lastRow As Long
    
    ' Last row in the entire used range of the sheet
    lastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
    
    For i = 1 To lastRow
        ' Do something with Cells(i, 1)
        Debug.Print Cells(i, 1).Address
    Next i
End Sub

Note that UsedRange.Rows.Count returns the number of rows in the used range, but if the used range doesn’t start at row 1, you need to add the starting row offset. The formula above adjusts for that.

Common Pitfalls and How to Avoid Them

  • Hard-coded row counts – Using For i = 1 To 1000 breaks when data grows. Always calculate the last row dynamically.
  • Modifying the range while looping – If you delete rows inside a For Each loop, you’ll skip rows. Use a For loop from bottom to top (For i = lastRow To 1 Step -1) when deleting.
  • Slow performance – Accessing cells one by one is slow. Consider reading the entire column into an array, processing it, and writing back. Example: Dim arr As Variant: arr = Range("A1:A" & lastRow).Value then loop through the array.
  • Forgetting to qualify worksheets – If you don’t specify a worksheet, VBA uses the active sheet, which can cause errors if the user switches sheets. Use ThisWorkbook.Sheets("Sheet1") or a variable.

Practical Example: Summing Values in a Column

Suppose you have numbers in column A (A1:A100) and you want to sum only the positive values. Here’s a loop that does that:

Sub SumPositiveValues()
    Dim i As Long
    Dim lastRow As Long
    Dim total As Double
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    total = 0
    
    For i = 1 To lastRow
        If IsNumeric(Cells(i, 1).Value) Then
            If Cells(i, 1).Value > 0 Then
                total = total + Cells(i, 1).Value
            End If
        End If
    Next i
    
    MsgBox "Sum of positive values: " & total
End Sub

This example checks for numeric values to avoid errors from text cells. The result is shown in a message box. For larger datasets, using Application.WorksheetFunction.SumIf would be faster, but the loop demonstrates the logic.

Alternatives to Looping: Built-in Functions and Filters

Before writing a loop, consider whether Excel’s built-in features can do the job more efficiently:

  • AutoFilter – Filter rows based on criteria, then perform actions on visible cells only. This avoids looping entirely.
  • Worksheet functions – SUMIF, COUNTIF, VLOOKUP can handle many tasks without VBA.
  • Array formulas – In modern Excel, dynamic arrays can spill results across multiple cells.
  • Power Query – For complex data transformations, Power Query is often faster and more maintainable than VBA loops.

Loops are still necessary when you need to perform actions that built-in functions can’t do, like sending emails based on cell values or generating custom reports.

Performance Tips for Large Columns

If you’re looping through thousands of rows, these tips will keep your macro responsive:

  • Turn off screen updating – Application.ScreenUpdating = False at the start, and set it back to True at the end.
  • Disable automatic calculations – Application.Calculation = xlCalculationManual and restore it after the loop.
  • Use arrays – Read the range into an array, process in memory, and write back in one operation.
  • Limit the range – Only loop through the used rows, not the entire column.

Example of array processing:

Sub FastLoopWithArray()
    Dim dataArr As Variant
    Dim i As Long
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    dataArr = Range("A1:A" & lastRow).Value
    
    For i = LBound(dataArr) To UBound(dataArr)
        If IsNumeric(dataArr(i, 1)) Then
            dataArr(i, 1) = dataArr(i, 1) * 2
        End If
    Next i
    
    Range("A1:A" & lastRow).Value = dataArr
End Sub

This doubles every numeric value in column A. Writing the array back at once is much faster than writing cell by cell.

Frequently Asked Questions

How do I loop through a column and skip blank cells?

Use an If statement inside the loop to check if the cell is empty. For example: If cell.Value <> "" Then before processing. Alternatively, use If Not IsEmpty(cell) Then to handle cells that contain formulas returning empty strings.

Can I loop through a column and delete entire rows that meet a condition?

Yes, but you must loop from the bottom up to avoid skipping rows. Use For i = lastRow To 1 Step -1 and then Rows(i).Delete when the condition is met. Always save a copy of your workbook before running a delete macro, because it cannot be undone with Ctrl+Z.

What is the difference between Cells and Range in a loop?

Cells(row, column) returns a single cell using numeric indices, while Range("A1") uses letter notation. In loops, Cells(i, 1) is convenient because i is a variable. Both refer to the same cell, but Cells is often easier to use in a loop.

Why is my loop running slowly on a large column?

Slow loops usually come from accessing the worksheet repeatedly. Turn off screen updating and calculation, and consider reading the data into an array first. Also, make sure you’re not looping to row 1048576 when you only have 100 rows.

Can I loop through a column in Google Sheets with VBA?

No, VBA is only for Microsoft Excel. In Google Sheets, you use Google Apps Script, which is similar to JavaScript. The syntax is different, but the logic of looping through rows is the same. For example, you’d use getRange("A1:A") and a for loop.

How do I stop a loop if a certain value is found?

Use Exit For inside the loop. For example, if you’re searching for the text “Stop”, you can check If cell.Value = "Stop" Then Exit For. This exits the loop immediately and continues with the rest of the macro.

Safety and Best Practices When Running Macros

Before running any VBA macro that modifies data, always save a backup copy of your workbook. Macros cannot be undone with Ctrl+Z, and a small mistake could overwrite important data. Also, test your loop on a small sample or a copy of the data first. If you’re working with a shared workbook, inform others before running a macro that changes data. Finally, enable macros only from trusted sources—Excel will warn you if a workbook contains macros, and you should only enable them if you trust the file’s origin.

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.