A VBA For Each loop repeats an action for every item in a collection or array, without requiring you to manage an item number. In Excel, it is commonly used to process each cell in a range or each worksheet in a workbook; the basic pattern is For Each item In group, followed by the statements to run and Next item.
Basic syntax and a simple example
The loop assigns the next member of the group to its control variable, runs the statements between For Each and Next, and continues until it has visited every member. Declare the control variable before the loop. For a collection of Excel objects, use an appropriate object type such as Range or Worksheet; for an array, use a Variant.
Sub ListCellValues()
Dim cell As Range
' Visit each cell in the specified range.
For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
Debug.Print cell.Value
Next cell
End Sub
This example sends the values from A1 through A10 on Sheet1 to the Immediate window in the Visual Basic Editor. It does not change the worksheet. The range is qualified with ThisWorkbook so the macro refers to the workbook containing the code, rather than whichever workbook happens to be active. If the worksheet has a different name, change "Sheet1" to its actual tab name.
The control variable, cell, represents one cell at a time. You can use it to read or change properties such as Value, Font.Bold, or Interior.Color. The loop ends after it reaches the final cell in the specified range.
How a For Each loop works
A collection or array supplies the items in sequence. The loop does not require you to write a starting index, increment a counter, or check a stopping condition. That makes the code easier to read when the task is simply “do this for every member.” In Excel VBA, common groups include a range’s cells, a workbook’s worksheets, and objects held in a VBA Collection.
The control variable must be suitable for the type of group being traversed. Use an object variable for objects, such as a Worksheet variable for worksheets. A Variant is suitable when iterating over an array and can also hold many different kinds of values. VBA assigns each item to the control variable as the loop proceeds; you do not use Set in the loop statement.
The loop variable is not a numeric position. If you need the item’s index—for example, to compare an element with the one before it—use a conventional For...Next loop or maintain a separate counter. A For Each loop is about visiting items, not exposing their positions.
Looping through cells in an Excel range
A Range object can represent one cell, a rectangular block, or a range made up of multiple areas. A For Each loop over a range visits its cells individually. This is useful for tasks such as checking for blanks, applying formatting, or changing values based on a condition.
Sub MarkNegativeValues()
Dim cell As Range
' Highlight negative numbers in the specified range.
For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("B2:B50")
If IsNumeric(cell.Value) Then
If cell.Value < 0 Then
cell.Interior.Color = vbYellow
End If
End If
Next cell
End Sub
The numeric check prevents a comparison with text from producing an unintended type problem. Blank cells are treated as numeric in some VBA expressions, so if blanks should be ignored, add a check such as If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then before comparing. For ranges containing formulas that return an empty string, IsEmpty may not be enough: the cell contains a formula, so check its displayed value as well.
For a large range, a cell-by-cell loop can be slower than using Excel’s built-in operations or reading values into an array and processing them in memory. For moderate ranges and logic that varies by cell, however, For Each is often clear and practical. If the macro changes cell values while looping, keep in mind that later checks may then see the changed values.
Looping through worksheets and other objects
Workbooks expose collections of worksheets, so For Each is a natural way to apply the same operation to every sheet. For example, the following macro prints each worksheet name:
Sub ListWorksheetNames()
Dim ws As Worksheet
' Visit every worksheet in the workbook containing this code.
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub
ThisWorkbook.Worksheets contains worksheet objects in that workbook. It is not identical to ThisWorkbook.Sheets, which can also include chart sheets. Use the collection that matches what you mean: if the operation requires a worksheet’s cells, the Worksheets collection is usually the clearer choice. To loop through a different open workbook, qualify its collection with that workbook object rather than relying on the active workbook.
Inside the loop, ws refers to the current worksheet, so you can use expressions such as ws.Range("A1").Value. Explicit qualification matters in macros that may run while several workbooks are open; unqualified references can accidentally target the active sheet or workbook.
Using For Each with arrays
For Each can iterate through the elements of an array. Declare the control variable as a Variant, which can receive each array element in turn. This is handy when the values matter but their positions do not.
Sub PrintRegions()
Dim regions As Variant
Dim region As Variant
' Array returns a Variant array of values.
regions = Array("North", "South", "East", "West")
For Each region In regions
Debug.Print region
Next region
End Sub
The example prints four region names. It does not change the array, and the loop does not provide an index for each name. If your task needs to update a particular position, compare neighboring elements, or work backward through the array, a numeric For...Next loop is generally more appropriate.
For Each compared with For…Next
Choose a loop based on what the task needs. For Each is usually the most readable choice when every item should receive the same treatment. A numeric For…Next loop is better when the position itself controls the work, when you need a specific start and end index, or when you need to step through indices in reverse.
- Use For Each to format every cell in a range, inspect every worksheet, or process every value in an array.
- Use For…Next to work with array positions, process every second row, count from a chosen starting point, or delete rows from bottom to top.
- Use a Do loop when the number of repetitions depends on a condition that may not be known in advance.
A common misconception is that For Each is always faster. Its main advantage is simpler item-by-item code, not a guarantee of better performance. Excel object-model calls—such as reading or writing worksheet cells—can dominate runtime. For very large datasets, transferring a range’s values to a VBA array, processing the array, and writing results back in a block may be more efficient, although that approach requires extra care with array dimensions and worksheet errors.
Common errors and how to avoid them
Many For Each problems come from a mismatch between the loop variable and the group, or from an incorrectly qualified Excel object. Check these issues when a loop fails or appears to process the wrong items:
- Wrong control-variable type: Use
Rangefor cells,Worksheetfor worksheets, andVariantfor array elements. A variable declared as a numeric type cannot represent a worksheet object. - Wrong collection: Confirm that the expression after
Inis the collection or array you intend to traverse, such asThisWorkbook.Worksheets. - Unqualified references: A bare
Range("A1:A10")can refer to the active sheet. Qualify it with a workbook and worksheet when the target must be fixed. - Unexpected values: Cells may contain errors, text, blanks, or formulas returning empty text. Test values before applying operations that expect a particular type.
- Changing a collection during iteration: Removing items from a collection while traversing it can cause items to be skipped or the loop to behave unexpectedly. If deleting worksheet rows, a reverse-indexed For…Next loop is often safer.
- Expecting an index: For Each does not tell you the item’s position. Keep a separate counter or use an indexed loop if position matters.
You can use Exit For to stop the current For Each loop early, for example after finding a match. In nested loops, it exits only the innermost loop that contains it. To stop an outer loop as well, use a flag or restructure the procedure. A loop that completes normally reaches Next for each item; the loop variable should not be treated as a reliable item after the loop has finished.
Running VBA safely in Excel
VBA macros run in the Excel desktop application. Excel for the web does not run VBA macros, so open the workbook in desktop Excel to use this code. In Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11. On Mac, use Option+F11 where supported by the keyboard; some keyboards require Fn as well, or use Excel’s menu to open the Visual Basic Editor.
Save a macro-enabled workbook as an .xlsm file if you need to keep its VBA code. Before running a macro, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z (or Command+Z on Mac), and a loop that changes many cells may make broad changes quickly. Run macros only from sources you trust, and review code before enabling it. To run a procedure from Excel on Windows, use Alt+F8, select the macro, and choose Run; on Mac, the Macro command is available from Excel’s menu.
Frequently Asked Questions
What does For Each do in VBA?
It runs a block of statements once for each item in a collection or array. In Excel, that can mean each cell in a range or each worksheet in a workbook.
What variable type should I use in a For Each loop?
Use an object type that matches the items, such as Range or Worksheet, when looping through Excel objects. Use a Variant when looping through an array.
Can I use For Each with a range of cells?
Yes. Set the control variable to Range and put the range after In. The loop then visits the range’s cells one at a time.
How do I stop a For Each loop early?
Use Exit For inside the loop when the condition for stopping is met. In nested loops, it exits only the innermost loop.
Can I change cells while looping through a range?
Yes, a loop can read and write cell properties. But changing values may affect later tests, and deleting rows can disrupt traversal. For structural changes such as deleting rows, consider looping through row numbers in reverse instead.
Is For Each better than For…Next?
Neither is universally better. For Each is clearer when you need every item but not its position; For…Next is more suitable when an index, step size, or reverse order is important.
Related
- Power Query Split Cell Into Rows: Steps and Examples
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Create New Workbook With Name: A Practical GuideIn Excel VBA, create a workbook with Workbooks.Add, then give it a lasting name and location with SaveAs.…
- VBA Dictionary Split: Turn Text into Keys and ValuesIn VBA, use the Split function to break a string into an array, then add the pieces to…
- Get Workbook Path in VBA: FullName vs PathTo get a workbook’s folder in VBA, read its Path property; to get the complete location including the…
- Goal Seek VBA: Automate Excel What-If AnalysisIn Excel VBA, use the GoalSeek method on a formula cell to make it reach a target by…
- Excel VBA: Protect a Sheet and Manage Permissions SafelyYes. In Excel VBA, protect a worksheet with its Protect method, for example Worksheets("Entry").Protect Password:="demo-pass". Add options such…
- Excel VBA Open Workbook Dialog: A Practical How-ToTo display a workbook picker in Excel VBA, call Application.GetOpenFilename, check whether the user cancelled, and pass the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.