Skip to main content

How to loop through rows until blank in Excel column?

Here is a long column with data which is including some empty cells, and you want to loop through the rows until meeting blank cell. In Excel, there is no built-in feature can handle this job, but I can introduce some macros to do you a favor.

Loop through rows until blank with VBA

arrow blue right bubble Loop through rows until blank with VBA

1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste below code to the blank script.

VBA: Loop until blank

Sub Test1()
      Dim x As Integer
      Application.ScreenUpdating = False
      ' Set numrows = number of rows of data.
      NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
      ' Select cell a1.
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
         ' Insert your code here.
         ' Selects cell down 1 row from active cell.
         ActiveCell.Offset(1, 0).Select
      Application.ScreenUpdating = True
End Sub

doc loop until blank 1

In the code, A1 is the first cell you want to loop from, you can change it as you need.

3. Press F5 key to begin looping the column, then the cursor will stop at the first met blank cell.
doc loop until blank 2

Note: If you want to loop through data until meet continuous blank cells, you can use this macro code.

Sub LoopThroughUntilBlanks()
      ' Select cell A2, *first line of data*.
      Dim xrg As Range
      On Error Resume Next
      Set xrg = Application.InputBox _
        (Prompt:="first cell select..", Title:="Kutools for Excel", Type:=8)
      xrg.Cells(1, 1).Select
      ' Set Do loop to stop when two consecutive empty cells are reached.
      Application.ScreenUpdating = False
      Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
         ' Insert your code here.
         ' Step down 2 rows from present location.
         ActiveCell.Offset(2, 0).Select
      Application.ScreenUpdating = False
End Sub

Then you need to select the first cell you want to loop from in the Kutools for Excel dialog, click OK, then the cursor stops in the first continuous blank cells.

doc loop until blank 3 doc loop until blank 4

Easily Combine multiple sheets/Workbook into one Single sheet or Workbook

To combinne multiples sheets or workbooks into one sheet or workbook may be edious in Excel, but with the Combine function in Kutools for Excel, you can combine merge dozens of sheets/workbooks into one sheet or workbook, also, you can consolidate the sheets into one by several clicks only.  Click for full-featured 30 days free trial!
combine sheets
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Best Office Productivity Tools

Supercharge Your Spreadsheets: Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns     Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (7)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
The first VBA produces the wrong result in cases when there is one or zero rows of data.

You probably need something like

If Range("A1").Value = "" Then
NumRows = 0
ElseIf Range("A1").Offset(1, 0).Value = "" Then
NumRows = 1
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
End If
This comment was minimized by the moderator on the site
The loop works for me except it loops through every single row regardless of it being blank or not.
This comment was minimized by the moderator on the site
Lets say I have a ton of rows...any tricks on making this run faster?
This comment was minimized by the moderator on the site
Can you please explain what is the condition to stop looping? What makes you break out of the loop in Test1()?
This comment was minimized by the moderator on the site
It will stop while meeting the first blank in the column
This comment was minimized by the moderator on the site
Its looping a column not a row
This comment was minimized by the moderator on the site
In order to loop through a column, you must go by row number.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations