Vba loop through rows?

To loop through rows in Excel with VBA, use a For loop from the first data row to the last used row, and read or change cells using the loop counter. For example, find the last row in column A, then process rows 2 through that row; this pattern works well when row 1 contains headers.

A basic row-by-row loop

In this example, column A is assumed to contain an entry on every data row, row 1 contains headings, and column C contains values to check. The macro writes Review in column D when a numeric value in column C is negative. If C2 is 12, C3 is -3, and C4 is 0, the result is Review in D3; the other cells in column D are left unchanged.

Sub FlagNegativeValues()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim valueToCheck As Variant

    ' Use the Data worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last nonblank cell in column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Start at row 2 because row 1 contains headings.
    For r = 2 To lastRow
        valueToCheck = ws.Cells(r, "C").Value
        If IsNumeric(valueToCheck) Then
            If CDbl(valueToCheck) < 0 Then
                ws.Cells(r, "D").Value = "Review"
            End If
        End If
    Next r
End Sub

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. The macro also assumes that a worksheet named Data exists; change that sheet name in the code if your data is elsewhere. Save the workbook in a macro-enabled format, such as .xlsm, if you need to keep the VBA code.

How the loop and last-row check work

The variable r holds the current worksheet row number. Each pass through For r = 2 To lastRow increases it by one, so expressions such as ws.Cells(r, "C") refer to C2, then C3, then C4, and so on. The loop stops after processing the value of lastRow. When the last row is below 2, the loop has no iterations, which is useful for a sheet with headers but no data.

The expression ws.Cells(ws.Rows.Count, "A").End(xlUp).Row starts at the bottom of column A and moves upward to the next nonblank cell. It returns that cell’s row number. This is generally safer than assuming a fixed row count, because the macro can accommodate a list that grows or shrinks. It does not determine the last row across every column: it uses column A as its reference.

Choose a reference column that is reliably filled for every record. If column A has blanks near the bottom but column B is always populated, use B in the last-row expression instead. If the sheet is entirely empty, the expression usually returns row 1, so a loop beginning at row 2 simply does nothing.

Choosing the right loop pattern

A counter-based For loop is a good default when you need the row number, need to move among several columns in the same record, or might process rows in reverse order. It makes the relationship between the worksheet row and the code explicit. Use Long for row counters and row numbers; Excel worksheets have more rows than the maximum value of VBA’s smaller Integer type.

If you only need to inspect each cell in one range, a For Each loop can be clearer. With headers in row 1 and data in A2:A20, this example checks each cell in that range and marks negative numeric values in the adjacent cell. For an empty or variable-length list, first calculate the last row as in the earlier example and build the range from that result.

Sub CheckOneColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range

    ' Set the worksheet and find the final entry in column A.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Check each data cell, if any rows exist below the header.
    If lastRow >= 2 Then
        For Each cell In ws.Range("A2:A" & lastRow)
            If IsNumeric(cell.Value) Then
                If CDbl(cell.Value) < 0 Then
                    cell.Offset(0, 1).Value = "Review"
                End If
            End If
        Next cell
    End If
End Sub

Here cell.Offset(0, 1) means the cell on the same row, one column to the right. A range-based loop is convenient for a single column, while a row counter is usually easier to follow when the decision depends on several fields, such as a customer name in A, a date in B, and an amount in C.

Adding conditions and updating cells

Most useful row loops combine a test with an action. The test might look for a status such as Paid, a date within a period, a blank field, or a value above a threshold. Use the same row counter to read all relevant columns so the values belong to the same record. For example, if you check a status in column B and write a note in column E, use ws.Cells(r, "B").Value and ws.Cells(r, "E").Value within the same pass.

Be deliberate about what happens when the test is false. The first macro only writes Review for negative values; it does not erase an existing note when a value later becomes nonnegative. That behavior avoids deleting unrelated content, but it can leave stale flags after data changes. If the macro owns the entire output column, you can clear or replace its result deliberately. Do not clear a column that also contains user-entered information.

When comparing text, decide whether capitalization should matter. VBA’s comparison behavior can be affected by the module’s Option Compare setting, so for predictable matching you can normalize both values with functions such as UCase or LCase. Also account for cells that contain errors: converting an error value with CDbl can fail, so test for errors with IsError before converting when the source column may contain Excel errors.

Deleting rows safely

Deleting rows while counting upward can skip records. When row 5 is deleted, the former row 6 moves into row 5; if the counter then advances to 6, that moved record is never checked. The usual solution is to loop upward from the last row to the first data row, so deleting a row does not shift any not-yet-checked row number.

For example, with a header in row 1 and a deletion condition based on column B, count down from the last row to 2. Confirm the condition carefully before deleting, and use a copy of the workbook because deletion is a destructive change. If retaining the records matters, consider marking them for review or copying them to another sheet instead of removing them.

Sub DeleteRowsMarkedRemove()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long

    ' Work on a named sheet and use column B to find the end.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ' Count backward so deleting a row does not skip the next record.
    For r = lastRow To 2 Step -1
        If ws.Cells(r, "B").Value = "Remove" Then
            ws.Rows(r).Delete
        End If
    Next r
End Sub

Speed and larger worksheets

For a modest list, reading and writing cells inside a loop is often straightforward and fast enough. On a large worksheet, repeated communication between VBA and the worksheet can become a bottleneck, especially when the loop reads many cells one at a time. A common optimization is to read a range into a VBA array, process the values in memory, and write results back in a block. That approach can be faster, but it requires more careful handling of range dimensions and output, so it is best introduced when a simple loop is demonstrably too slow.

Another common performance measure is temporarily turning off screen updating during a long macro, then turning it back on when the work finishes. If you also change calculation or event settings, restore their original values even if an error occurs; otherwise Excel can be left in an unexpected state. For short macros, the added error-handling code may not be worth the complexity. Avoid adding optimization settings automatically before you know they are needed.

Common mistakes and troubleshooting

Wrong starting row: Starting at 1 processes the heading as data. Start at 2 when row 1 is the header, or use the actual first data row for your layout.
Wrong last-row column: A blank in the reference column can make the calculated endpoint earlier than expected. Use a column that is filled for each record.
Misspelled worksheet name: A reference such as Worksheets("Data") fails if the tab has a different name. Check the sheet tab and keep the quoted name exact.
Using Integer for row numbers: Use Long so the counter can represent worksheet row numbers.
Running the macro in the wrong workbook: ThisWorkbook means the workbook containing the VBA code, not necessarily whichever workbook is currently active. This is usually safer for a macro stored with its data.
Expecting Ctrl+Z to reverse a macro: Excel’s undo history does not reverse VBA actions. Save a separate copy before running code that changes, clears, or deletes data.

To run a macro in desktop Excel for Windows, open the Visual Basic Editor from Developer > Visual Basic, put the procedure in a standard module, and run it from the editor or Excel’s macro interface. If the Developer tab is not visible, it can be enabled through Excel Options. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros; use desktop Excel for those tasks. The VBA logic is similar in Excel for Mac, though the editor and some interface steps differ.

Frequently Asked Questions

How do I loop through rows until the first blank cell?

You can use a counter loop and exit when the chosen column is blank, but this stops at the first blank and will not process later rows. That is appropriate only when a blank reliably marks the end of the list. If records may contain gaps, find the last used row and loop through the full range instead.

Can I loop through only visible rows?

Yes, but a standard loop from row 2 to the last row visits hidden and filtered-out rows too. To process only visible cells, work with a range’s visible cells using Excel’s SpecialCells functionality, and account for the possibility that no cells meet the visible-cell selection. Decide whether manually hidden rows should be included before choosing the approach.

Should I use For or For Each for worksheet rows?

Use For with a row counter when the code needs row numbers, accesses several columns per record, or deletes rows in reverse order. Use For Each when you are iterating through the cells of one range and the cell object itself is convenient. Neither form is universally faster or better for every task.

Why does my loop stop before the final record?

First check which column determines lastRow. If that column is blank in the final records, the loop endpoint will be too early. Also check whether the data begins below row 2, whether the worksheet name is correct, and whether a blank row is being treated as an intentional stopping point.

Can VBA loops run in Excel for the web?

No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; if the process must run in the browser, consider an alternative such as Office Scripts where available.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.