To loop over rows in Excel VBA, use a For...Next loop from the first data row to the last row you want to check. For example, find the last used cell in column A, then use For r = 2 To lastRow to process each row in turn.
Here is a basic pattern for a worksheet named Data with headers in row 1:
Option Explicit
Sub LoopOverRows()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
Debug.Print r, ws.Cells(r, "A").Value
Next r
End Sub
This example prints each value from column A to the Immediate window; it does not change the worksheet. Before running any macro that changes or deletes data, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.
What a row loop does
A row loop repeats instructions for a sequence of worksheet rows. On each pass, the loop variable—called r in the example—contains the current row number. Your code can use that number to read cells, test conditions, write results, or perform other work.
For instance, ws.Cells(r, "B").Value means the cell in column B of the current row. The worksheet variable ws identifies which sheet to use, so the code does not depend on whichever sheet happens to be active. This is safer than relying on ActiveSheet or selecting cells before working with them.
Most row loops start at row 2 when row 1 contains headings. If your data has several header rows, starts lower down, or includes a title above the table, set the starting row to match the actual layout. A loop does not know which rows are headers or records; those rules come from the code you write.
Find the last row to process
A loop needs an ending row. A common approach is to look upward from the bottom of a column that should contain a value for every record. In the example, ws.Cells(ws.Rows.Count, "A").End(xlUp).Row returns the row number of the last non-empty cell in column A. It works like starting at the bottom of that column and pressing Ctrl+Up.
Choose a dependable key column, such as an ID or required name field. If column A can be blank even when other columns in that record are filled, using A may stop the loop too early. Instead, select a column that is consistently populated, or determine the last row using a different strategy suited to the sheet. Formatting alone can make a sheet appear used far below its actual data, so a large UsedRange is not always a reliable substitute.
If there may be no data rows, the basic loop is still safe when the last row is 1: a loop from row 2 to row 1 with the default positive step has no iterations. For a procedure with more complicated bounds, explicitly check that the last row is at least the first data row. That makes the intended behavior clearer and helps prevent accidentally building a range with reversed or invalid endpoints.
Use For…Next for ordinary row-by-row work
For...Next is usually the clearest choice when you need a row number, want to move through rows in order, or need to count backward. This example checks a numeric value in column B and writes a label in column C. It assumes row 1 has headings and the data sheet is named Data.
Option Explicit
Sub LabelLargeOrders()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
If IsNumeric(ws.Cells(r, "B").Value) Then
If CDbl(ws.Cells(r, "B").Value) >= 1000 Then
ws.Cells(r, "C").Value = "Review"
Else
ws.Cells(r, "C").Value = "Standard"
End If
Else
ws.Cells(r, "C").Value = "Check amount"
End If
Next r
End Sub
The numeric test prevents a conversion error if a cell in column B contains text or is blank. The result is written to the same row, which keeps each label aligned with the record that was checked. If column C already contains important information, choose another output column or make a backup before running the procedure.
Choose between row numbers and For Each
Use For...Next when the row number matters—for example, when you need to update several columns on the same row, refer to a row in another range, or delete rows. A For Each loop is convenient when you only need to inspect each cell in one column or range. The loop variable is then a Range object rather than a number.
For example, a For Each cell In ws.Range("A2:A" & lastRow) loop can test each cell in column A. Refer to cell.Row to get its row number and cell.Value to read its contents. If the last row can be above 2, check that there are data rows before constructing the range; otherwise a range expression can cover unintended cells.
There are also Do While and Do Until loops. They suit cases where the stopping condition is not known in advance, such as continuing until a marker is found. For a fixed table with a known first and last row, For...Next is generally easier to read. Avoid a loop whose condition never changes: a Do While loop that never reaches its stopping condition can run indefinitely.
Skip blanks and handle special rows
Real worksheets often have blank cells, incomplete records, or notes mixed in with data. Put a condition inside the loop so the macro processes only rows that meet your rule. For example, test whether the ID cell is empty before reading the rest of the record. Use Len(Trim$(CStr(value))) = 0 when cells might contain spaces that should count as blank; a direct check for an empty cell does not treat every space-containing cell as empty.
Be deliberate about what counts as blank. A formula that returns an empty string is not the same thing as a truly empty cell for every VBA test. If formulas are present, inspect the cell’s Value or Value2 according to whether you care about its displayed result, and test the behavior with representative data. Similarly, dates and currency values are stored as numbers, so comparisons should match the underlying value and the intended business rule.
When skipping a row, keep the test close to the work it controls. Clear conditions make the macro easier to audit later: readers can see which field determines whether a record is included, and whether a missing value means skip, flag, or report an error. Do not silently skip records if a blank field should trigger follow-up; writing a warning to a separate column may be more useful.
Delete rows without skipping records
Deleting rows while counting upward is a common source of mistakes. After a row is deleted, the following row moves into its place, but the loop advances to the next row number. That can cause the newly shifted row to be missed. When deleting based on a condition, loop from the last row up to the first data row using Step -1.
This example deletes rows where column A is blank. It is destructive, so save a separate copy of the workbook first and confirm that blank IDs really mean the whole row should be removed.
Option Explicit
Sub DeleteRowsWithBlankIDs()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
' Save a separate copy of the workbook before running this macro.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = lastRow To 2 Step -1
If Len(Trim$(CStr(ws.Cells(r, "A").Value))) = 0 Then
ws.Rows(r).Delete
End If
Next r
End Sub
Filtering and deleting visible rows manually may be preferable when the rule is simple and you want to review the selected records first. VBA is useful when the same operation must be repeated consistently, but automation does not make a deletion reversible. Ctrl+Z will not restore changes made by a macro.
Improve speed and reduce mistakes
For small lists, reading and writing cells directly inside a loop is usually simple and adequate. For very large datasets, repeated communication between VBA and worksheet cells can become slow. A common optimization is to read a block of cells into a VBA array, process the values in memory, and write results back in a block. This takes more code and requires careful handling of array dimensions, so it is worth using when speed is a real concern rather than as a first step.
- Use a worksheet variable and fully qualified references such as
ws.Cells(r, "A"); avoid depending on selections or the active sheet. - Declare variables, especially the row counter as
Long. Excel worksheets can have more rows than anIntegercan represent. - Test the macro on a small copy of the data, including blank cells, text in numeric columns, and the final record.
- For procedures that change application settings such as screen updating or calculation, restore the original settings even if an error occurs.
Also consider where the code is stored. ThisWorkbook means the workbook that contains the VBA code. If the macro is stored in the Personal Macro Workbook but should operate on the currently open data file, the workbook reference needs to be chosen accordingly; otherwise the code may look for the Data sheet in the wrong file. Naming the target workbook and worksheet explicitly is safer than assuming they are active.
Run the macro and troubleshoot common problems
In desktop Excel for Microsoft 365 on Windows, save a macro-enabled workbook as .xlsm, open the Visual Basic Editor with Alt+F11, and use Insert > Module to add a standard module. Paste the procedure into the module, change the worksheet name and column references to match your data, then run it from the editor or Excel’s macro controls. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
Excel for Mac also supports VBA in the desktop app; use the Developer tab’s Visual Basic command when available. The keyboard shortcut can depend on the Mac keyboard and function-key settings. Excel for the web cannot create, edit, or run VBA macros, so open the workbook in the desktop application. Keep macro security in mind: only enable macros in files from sources you trust.
If the code stops with a subscript error, check that the sheet name in the code exactly matches the tab name. If it processes no rows, verify the chosen last-row column and the starting row. If it processes too many, check whether formulas or stray values extend that column farther than expected. When execution pauses on a line, the highlighted statement and the error message can help identify a missing worksheet, an invalid range, or a value that needs validation.
Frequently Asked Questions
How do I loop through every used row in Excel VBA?
Choose a column that reliably marks the records, find its last non-empty row with Cells(Rows.Count, column).End(xlUp).Row, and use a For...Next loop from the first data row through that number. A key column is usually more dependable than treating every formatted cell as data.
How do I loop through rows but skip the header?
Start the loop at row 2 when row 1 contains headings, as in For r = 2 To lastRow. If your headings occupy more than one row, set the first data row to the actual row where records begin.
Should I use For Each or For…Next for rows?
Use For...Next when you need row numbers, need to move backward, or are deleting rows. Use For Each when inspecting each cell in a particular range is more natural and the cell’s row number is not central to the logic.
Why does my VBA loop miss every other row when deleting?
Deleting a row shifts the next row upward into the deleted row’s number. A loop moving forward then advances past that shifted row. Loop from the last row upward with Step -1 so deletions do not change the positions of rows still waiting to be checked.
Can I run an Excel VBA row loop in Excel for the web?
No. Excel for the web does not run VBA macros. Open the file in desktop Excel for Windows or Mac to use the Visual Basic Editor and run the macro.
Related
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? Causes and Fixes
- Excel Spreadsheet Grouping Rows: How to Create and Use It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code to Save a Workbook in Excel: Practical ExamplesTo save the workbook that contains your VBA code, use ThisWorkbook.Save; to save the active workbook, use ActiveWorkbook.Save.…
- Excel Macro: Refresh All Data ConnectionsYes. In Excel for Microsoft 365 on Windows, a VBA macro can refresh the workbook’s refreshable external data…
- Excel VBA Progress Bar Without a UserForm: 3 MethodsYes. Excel VBA can show progress without a UserForm by updating Excel’s status bar, writing progress to a…
- VBA Array Notation: Syntax, Bounds, and ExamplesIn VBA, declare an array with parentheses after its name, such as Dim scores(1 To 5) As Long,…
- Excel VBA Find Numbers Only: Code, Search Tips, and FixesYes. To find cells that contain real numeric values—not numbers stored as text—loop through the range and check…
- Can You Make Macros in Google Sheets? How They WorkYes. You can make macros in Google Sheets by recording a sequence of actions in the browser; Sheets…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.