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 Whilefor simplicity. - Large datasets (over 100,000 rows): Use the
Find Last Rowmethod to minimize reads. - Data with blank rows in the middle: Use
Do Whilewith a check on a column that is always filled, or use the last row method and add anIfto 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
rinside the loop, it runs forever. Always includer = 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.IsEmptywill returnFalsefor such cells, so the loop continues. To stop on those, useLen(Cells(r,1).Value) = 0. - Using
SelectorActivate: Avoid these; they slow down the macro and can cause errors. Work directly with theCellsorRangeobjects.
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 = Falseat the start, and set it back toTrueat the end. - Disable automatic calculations: If your sheet has formulas, set
Application.Calculation = xlCalculationManualbefore 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
- Excel Spreadsheet Checklist Template: How to Build One
- Excel Strikethrough: How to Add It & Shortcuts
- Excel Strikethrough: How to Apply & Remove It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Left Function: Syntax, Examples, and TipsIn Excel VBA, Left returns a specified number of characters from the start of a string: for example,…
- Excel VBA: Delete a Range of Rows by Number or MatchTo delete worksheet rows with Excel VBA, use Rows("3:7").Delete for rows 3 through 7, or qualify the range…
- Excel VBA Clear Formatting and Contents: Full GuideThe fastest way to clear both formatting and contents in Excel VBA is to use the Clear method…
- Excel VBA Union: Combining Multiple Ranges in a Single MacroIn Excel VBA, Union combines two or more ranges on the same worksheet into one Range object, even…
- Excel VBA String Character Limit: What Really AppliesIn Excel VBA, a variable-length String can hold roughly 2 billion characters in theory, but available memory and…
- Excel VBA Date Object: Dates, Times, and Examples ExplainedIn Excel VBA, Date is a built-in data type for storing a calendar date and, optionally, a time;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.