Excel VBA: Loop Through Rows Until Empty (4 Ways)

VBA & macros · Updated November 22, 2026

The simplest way to loop through rows until an empty cell is found is to use a Do While loop that checks a specific column for a blank cell, like Do While Cells(r, 1).Value <> "". This is the most direct and readable method for most cases. Below, we cover four reliable techniques, how to choose between them, and the common pitfalls that trip up beginners.

Why Loop Until Empty Instead of a Fixed Range?

Hard-coding a range like Range("A1:A1000") works, but it breaks when your data grows or shrinks. Looping until an empty cell makes your macro adapt to the actual data size. This is especially useful when you import data from external sources where the row count changes daily. The empty-cell check treats a blank cell as a sentinel—a signal that the data has ended. This assumes your data has no gaps in the column you’re checking; if there are gaps, the loop will stop early.

Method 1: Do While with Cells (Most Common)

This method checks a single column (say, column A) for an empty value. It’s perfect when your data is contiguous from row 1 downward.

Sub LoopUntilEmpty_DoWhile()
    Dim r As Long
    r = 1
    ' Loop while column A is not empty
    Do While Cells(r, 1).Value <> ""
        ' Do something with the row, e.g., print value
        Debug.Print Cells(r, 1).Value
        r = r + 1
    Loop
End Sub

This starts at row 1 and increments r until it hits a blank cell in column A. Note: if you have a header row, start at r = 2. Also, cells that contain formulas returning an empty string ("") are not considered empty—the loop will continue. To treat those as empty, use IsEmpty(Cells(r, 1).Value) or check Len(Cells(r, 1).Value) = 0.

Method 2: Do Until IsEmpty (Alternative Syntax)

Some programmers prefer Do Until for readability. It does the same thing but with a different logical structure.

Sub LoopUntilEmpty_DoUntil()
    Dim r As Long
    r = 1
    ' Loop until an empty cell is found in column A
    Do Until IsEmpty(Cells(r, 1))
        ' Process the row
        Cells(r, 2).Value = Cells(r, 1).Value * 2
        r = r + 1
    Loop
End Sub

Here, IsEmpty returns True if the cell is truly empty (no value, no formula). This is slightly more precise than comparing to an empty string.

Method 3: Find the Last Used Row First

Instead of checking each cell, you can find the last non-empty row in a column and then loop from 1 to that row. This is faster for large datasets because it avoids checking every cell one by one.

Sub LoopWithLastRow()
    Dim lastRow As Long
    Dim r As Long
    ' Find the last used row in column A
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' Loop from row 1 to lastRow
    For r = 1 To lastRow
        ' Process each row
        Debug.Print Cells(r, 1).Value
    Next r
End Sub

The line Cells(Rows.Count, 1).End(xlUp).Row is a classic way to find the last row with data in column A. It goes to the very bottom of the sheet (row 1,048,576) and presses Ctrl+Up, landing on the last non-empty cell. This is efficient because it only reads one cell, not every cell. However, if your data has blank rows in the middle, this will include them in the loop, which might or might not be desired.

Method 4: For Each with UsedRange (Caution)

You can loop through all rows in the worksheet’s used range, but this is risky because it may include formatting-only cells that appear used.

Sub LoopUsedRange()
    Dim rng As Range
    Dim r As Long
    ' Set range to used rows in column A
    Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For Each cell In rng
        If cell.Value <> "" Then
            ' Process non-empty cells
            Debug.Print cell.Value
        End If
    Next cell
End Sub

This loops through each cell in the range, but you need an inner If to skip blanks. It’s less elegant and slower than the For loop with a last row. Use this only if you need to handle non-contiguous data or want to check every cell in a column regardless of blanks.

How to Choose the Right Method

Consider your data structure and performance needs:

  • Small datasets (under 10,000 rows): Any method works. Use Do While for simplicity.
  • Large datasets (over 100,000 rows): Use the Find Last Row method to minimize reads.
  • Data with blank rows in the middle: Use Do While with a check on a column that is always filled, or use the last row method and add an If to skip blanks.
  • Header row present: Start your loop at row 2, or adjust the starting row accordingly.

Common Problems and Solutions

Here are frequent issues beginners encounter:

  • Infinite loop: If you forget to increment r inside the loop, it runs forever. Always include r = r + 1.
  • Loop stops too early: If your data has a blank cell in column A but data in other columns, the loop will stop. Use a column that is guaranteed to have data in every row, or use End(xlUp) to find the last row based on a different column.
  • Cells with formulas returning “”: A formula like =IF(A1="","",B1) returns an empty string, not a truly empty cell. IsEmpty will return False for such cells, so the loop continues. To stop on those, use Len(Cells(r,1).Value) = 0.
  • Using Select or Activate: Avoid these; they slow down the macro and can cause errors. Work directly with the Cells or Range objects.

Performance Tips for Large Loops

If you’re processing thousands of rows, these tweaks will speed up your macro:

  • Turn off screen updating: Application.ScreenUpdating = False at the start, and set it back to True at the end.
  • Disable automatic calculations: If your sheet has formulas, set Application.Calculation = xlCalculationManual before the loop, then restore it.
  • Read data into an array: Instead of reading each cell individually, copy the entire range into a VBA array, loop through the array, and write back. This is much faster.
Sub FastLoopWithArray()
    Dim data As Variant
    Dim r As Long
    ' Read entire column A into array
    data = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For r = LBound(data) To UBound(data)
        If data(r, 1) <> "" Then
            ' Process value
            Debug.Print data(r, 1)
        End If
    Next r
End Sub

This reads all values at once, which is far more efficient than accessing cells one by one.

Frequently Asked Questions

How do I loop through rows until two columns are empty?

Use a condition that checks both columns, like Do While Cells(r, 1).Value <> "" Or Cells(r, 2).Value <> "". This continues as long as at least one of the two columns has data. Be careful with logic: use Or to continue if either is non-empty, And to continue only if both are non-empty.

What if my data starts at row 5, not row 1?

Simply set the starting row variable to 5: r = 5. The loop will then check from row 5 downward. Also, when finding the last row, you might want to use Cells(Rows.Count, 1).End(xlUp).Row which works regardless of start row.

Can I loop through rows until empty in a specific column, like column C?

Yes, just change the column index in the Cells reference. For column C, use Cells(r, 3) or Cells(r, "C"). The logic is identical.

Why does my loop skip the last row with data?

This usually happens when you use For r = 1 To lastRow but lastRow is calculated incorrectly. For example, if you use End(xlUp) on a column that has a blank in the last data row, lastRow will be smaller. Double-check your last row calculation by printing it.

Is there a way to loop through rows until empty without VBA?

Yes, you can use formulas like =IF(A1="","",...) or use Excel’s built-in features like Go To Special to select blanks, but for dynamic processing, VBA is the standard. You can also use Power Query to filter out blank rows, but that’s a different approach.

How do I stop an infinite loop if my code gets stuck?

Press Esc or Ctrl+Break (Windows) to interrupt execution. On Mac, press Cmd+.. To prevent infinite loops, always include an exit condition and test your code on a small dataset first.

Safety and Best Practices

Before running any macro, save a copy of your workbook. Macros cannot be undone with Ctrl+Z. If your loop writes data, consider using a backup. Also, be aware that Cells(Rows.Count, 1) refers to the last row of the worksheet, which is 1,048,576 in modern Excel. If you’re working with an older version, it might be 65,536. Use Rows.Count to be safe. Finally, always declare your variables with Dim and use Option Explicit at the top of your module to catch typos.

Related

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

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.