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 = 1tolastRow). 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 1000breaks when data grows. Always calculate the last row dynamically. - Modifying the range while looping – If you delete rows inside a
For Eachloop, you’ll skip rows. Use aForloop 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).Valuethen 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,VLOOKUPcan 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 = Falseat the start, and set it back toTrueat the end. - Disable automatic calculations –
Application.Calculation = xlCalculationManualand 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
- Excel Utility for E-Invoice: What It Does
- Excel Undo Encrypt with Password? How to Remove It
- Excel Truck Group Jobs: Pay, Hiring & Reviews
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Access VBA For Loop: Syntax, Examples, and Common ErrorsIn Access VBA, use a For...Next loop to repeat instructions a set number of times, and use For…
- Excel VBA: How to End a LoopTo end a VBA loop early, use Exit For inside a For loop or Exit Do inside a…
- VBA GoTo: Syntax, Examples, and Safer AlternativesIn VBA, GoTo is a statement, not a function: it transfers execution to a labeled line in the…
- Call a Macro from an Excel UserForm ButtonYes. In Excel for Microsoft 365 on Windows, put a call to a Public Sub in the UserForm…
- Excel VBA Macro to Send a Worksheet File via TeamsYes—but Excel VBA has no built-in command that sends a worksheet directly to Microsoft Teams. A practical Windows…
- Excel VBA Date Object: Dates, Times, and Examples ExplainedIn Excel VBA, Date is a built-in data type for storing a calendar date and, optionally, a time;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.