To loop through rows in Excel VBA, set a row counter and use a For...Next loop, usually from the first data row to the last used row. For example, find the last entry in column A, then inspect each row with For r = 2 To lastRow. This pattern lets a macro check values, write results, or take an action on every record.
What a row loop does
A row loop repeats the same instructions while moving through a worksheet one row at a time. The loop counter identifies the current row, and a cell reference combines that row number with a column. For example, ws.Cells(r, "B") means the cell in column B on row r. This is useful when a task depends on each record’s contents, such as marking orders over a threshold or checking whether a required field is missing.
In Excel for Microsoft 365 on Windows, VBA runs in the desktop application. Excel for the web can open a workbook containing VBA but cannot create, edit, or run its macros. On Mac, use desktop Excel; the VBA editor and some keyboard shortcuts differ, but the row-loop logic is the same.
Basic For Next loop
This example assumes a worksheet named Data, headers in row 1, IDs in column A, and amounts in column B. It writes “Review” in column C when an amount is at least 100. The last row is determined from column A, which should contain an ID for every record.
Sub MarkLargeAmounts()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
' Use the sheet in the workbook containing this macro.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Row 1 contains headings, so start with row 2.
For r = 2 To lastRow
If IsNumeric(ws.Cells(r, "B").Value) Then
If ws.Cells(r, "B").Value >= 100 Then
ws.Cells(r, "C").Value = "Review"
End If
End If
Next r
End Sub
The IsNumeric check prevents the comparison from treating text as an amount. If the data has no records and row 1 is the only populated row in column A, the loop starts at 2 and ends at 1, so it does not run. This example writes a result only for amounts meeting the condition; it does not clear existing notes in column C for other rows.
Find the last row reliably
A loop needs a stopping point. A common way to find it is to start at the bottom of a consistently populated column and move upward to the first occupied cell. In the example, ws.Rows.Count supplies the sheet’s final row number, and End(xlUp) works upward as if you pressed Ctrl+Up from the bottom of column A. Using ws keeps the lookup tied to the intended worksheet rather than whichever sheet happens to be active.
Choose a column that should have a value on every data row, such as an ID or date. If column A has gaps near the bottom but column B is consistently filled, use column B in the last-row expression instead. A blank cell inside the data does not stop the upward search, but a blank at the bottom of the chosen column can make the macro miss records that exist only in other columns. Formulas that display an empty string can still count as occupied cells, so the detected last row may include rows that look blank.
If no single column reliably marks the final record, use a more deliberate method—for example, search the relevant table or range for its last non-empty cell. Avoid using ActiveSheet.UsedRange as a universal last-row answer: formatting left far below the data can make a worksheet’s used range larger than the actual list.
Skip headers, blanks, and unsuitable values
Most worksheets have headings, so begin at row 2 rather than row 1. If there are two title rows or a note above the table, change the starting row to match the layout. A loop’s row numbers are worksheet row numbers, not positions relative to the data, unless you deliberately calculate an offset.
To skip records where a key field is blank, test that field inside the loop before processing the row. For example, place If Len(Trim$(CStr(ws.Cells(r, "A").Value))) > 0 Then around the work that should happen only when column A contains text. Trim$ removes leading and trailing spaces, while CStr converts the value to text for that check. For numeric columns, validate the value with IsNumeric before comparing it. These checks matter because worksheet cells can contain blanks, text, errors, or formulas, not just the values a macro expects.
Keep the action and the condition separate in your thinking: first decide which rows qualify, then decide what the macro should do to each qualifying row. This makes it easier to test the condition on a small sample and reduces accidental edits to headings or incomplete records.
Choose For, For Each, or Do While
A counted For...Next loop is usually the clearest choice when the row numbers are known. It makes the start, end, and direction explicit, and it is a good default for updating or checking records.
A For Each loop is convenient when you want to visit every cell in one column range. For example, with a worksheet variable and a defined range, you can loop through each cell and read or change its value. It is natural for cell-by-cell work, but it does not directly provide a row counter; use cell.Row when another column on that same row is needed. Do not delete worksheet rows while moving forward through a For Each range, because changing the range during iteration can cause rows to be skipped or the traversal to behave unexpectedly.
A Do While loop can suit a list that ends at the first blank in a particular column, but it stops at that first blank. That is the wrong choice if valid records may have gaps. When you know the last row or can calculate it once, a For...Next loop is generally easier to audit and less likely to stop prematurely.
Delete rows from the bottom upward
Deleting rows is a special case: after a row is removed, the rows below it move up. If a forward loop deletes row 5 and then increments to row 6, the original row 6 has shifted into row 5 and may never be checked. Count downward instead, so deleting a row does not change the row numbers still waiting to be examined.
This example assumes headers in row 1 and deletes records whose column B value is blank. It finds the last row using column A, so column A should identify each record.
Sub DeleteRowsWithBlankAmount()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
' Confirm the target sheet before removing records.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Work upward so deleted rows do not shift unchecked rows.
For r = lastRow To 2 Step -1
If Len(Trim$(CStr(ws.Cells(r, "B").Value))) = 0 Then
ws.Rows(r).Delete
End If
Next r
End Sub
Before running a deletion macro, confirm that “blank” means what you intend: a formula returning an empty string will be treated as blank by this check. Also confirm the target sheet and keep a backup. Deleting rows can affect formulas, references, filters, and other workbook content.
Make large loops faster
For a few hundred rows, reading and writing individual cells is often adequate and makes the code easy to understand. With very large ranges, repeated communication between VBA and the worksheet can take noticeably longer. A common improvement is to read a block into a VBA array, process the values in memory, and write a result block back in one operation.
Here is the general shape: load the needed range into a Variant array with Range.Value2, loop through the array’s indexes, store output in another array, then assign that output array to a same-sized range. Array indexes usually start at 1 for a range loaded this way. Add a guard for fewer than two rows before building a range from row 2 to the last row; otherwise a one-row sheet can produce an invalid range address. Also decide whether writing the output block should replace existing results, including cells that will receive blank values.
For a macro that changes the screen or calculation settings to run faster, save their original states and restore them even if an error occurs. Do not switch off settings casually: leaving calculation or screen updating disabled can confuse the next person using the workbook. For modest lists, a straightforward loop is often the safer trade-off.
Run and troubleshoot the macro safely
In desktop Excel, open the Visual Basic Editor from Developer > Visual Basic, insert a standard module, and paste the procedure there. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon on Windows; on Mac, use Excel > Settings (or Preferences, depending on the release) > Ribbon & Toolbar. Windows uses Alt+F11 to open the editor; on Mac, use Option+F11, sometimes with Fn depending on the keyboard. The Developer tab’s Macros command can run a selected procedure; Windows also uses Alt+F8 for the Macro dialog, while Mac keyboards commonly use Option+F8.
Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain VBA. Save a separate copy before running a macro that changes or deletes data: VBA changes cannot be undone with Ctrl+Z. Run only macros you trust, and do not lower macro security settings just to run code from an unknown source. If Excel reports “Subscript out of range,” check that the worksheet name in the code exactly matches a sheet tab. If nothing happens, verify the starting row, last-row column, condition, and whether the procedure is in a standard module.
Frequently Asked Questions
How do I loop through every used row in Excel VBA?
Find a last row in a reliably populated column with Cells(Rows.Count, column).End(xlUp).Row, then use a For loop from the first data row to that result. Qualify Cells and Rows with a worksheet variable so the macro does not depend on the active sheet.
How do I loop through rows until a blank cell?
Use a Do While loop if the first blank in the chosen column truly marks the end of the list. If blank cells can occur between valid records, find the last row and use a counted loop instead, then handle blanks with an If condition.
Can I loop through rows in reverse order?
Yes. Start at the last row and use Step -1, such as For r = lastRow To 2 Step -1. This is especially important when deleting rows because the remaining row numbers above the deleted row stay unchanged.
Why does my VBA loop skip rows?
A common cause is deleting rows while looping forward: the next row shifts into the position just processed. Loop backward for deletions. Other causes include choosing a last-row column with gaps, starting on the wrong row, or exiting a loop when it encounters a blank that is not actually the end of the data.
Can Excel for the web run a VBA row loop?
No. Excel for the web can retain a workbook that contains VBA, but VBA macros run in desktop Excel, including supported desktop versions for Windows or Mac. For browser-based automation, consider Office Scripts if they are available for your account and workflow.
Related
- Excel Split Cell Text Into Separate Cells: A Practical Guide
- Excel Split Cell Value by Delimiter: Methods and Tips
- Excel Split Cells Into Multiple Columns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA AutoFilter Field Criteria1: Values, Operators & ExamplesIn Excel VBA, Field identifies which column within the filtered range to filter, while Criteria1 supplies the first…
- Excel VBA Dictionary: How It Works and When to Use ItYes, Excel VBA can use a Dictionary on Windows, but VBA does not include a native Dictionary type.…
- VBA MsgBox Function: Syntax, Buttons, and ExamplesIn VBA, MsgBox displays a dialog box with a message and optional buttons, title, and icon. It also…
- Excel Macro Not Responding? Fix It SafelyIf an Excel macro is not responding, first give it a little time if Excel is still using…
- Excel VBA: Get a Range’s Length in Cells or CharactersIn Excel VBA, use Range.CountLarge to get how many cells a range contains; use Range.Rows.Count and Range.Columns.Count for…
- VBA Range in Excel: How to Reference CellsIn Excel VBA, a Range is an object that represents one cell or a group of cells. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.