In our sample dataset, some cells in the Salesman column are empty. We’ll use VBA loops to locate these empty cells.
Example 1 – Excel VBA to Loop through Known Number of Rows until Single Empty Cell
This method is suitable for smaller datasets where you know the number of rows. It finds the first empty cell in a specified column.
STEPS:
- Right-click the sheet tab and select View Code.
- The VBA window dialog box will pop up.
- Paste the following code.
Sub SingleEmpty()
Dim i As Integer
Application.ScreenUpdating = False
NumRows = Range("B5", Range("B5").End(xlDown)).Rows.Count
Range("B5").Select
For i = 1 To NumRows
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub
- Close the VBA window.
- Go to the Developer tab, select Macros.
- In the Macro dialog box, select SingleEmpty and press Run.
- The first empty cell (e.g., B7) will be highlighted.
Example 2 – VBA to Loop through Known Number of Rows until Continuous Blank Cells
This method finds the first empty cell followed by one or more empty cells.
STEPS:
- Right-click the sheet tab and select View Code.
- Paste the following code in the VBA window.
Sub ContinuousBlankCells()
Dim rg As Range
On Error Resume Next
Set rg = Application.InputBox _
(Prompt:="Select First Cell", Title:="LoopThroughUntilEmpty", Type:=8)
rg.Cells(1, 1).Select
Application.ScreenUpdating = False
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
ActiveCell.Offset(2, 0).Select
Loop
Application.ScreenUpdating = False
End Sub
- Close the VBA window.
- Go to Developer ➤ Macros.
- In the Macro dialog box, select ContinuousBlankCells and click Run.
- In the dialog box, select cell B5 in the Select First Cell box.
- Click OK.
- The code will highlight the desired cell (e.g., B9).
Read More: VBA to Loop through Rows of Table in Excel
Example 3 – Loop through Unknown Number of Rows with VBA until Empty Cell
STEPS:
- Right-click the sheet tab and select View Code.
- Paste the following code in the VBA window.
Sub UnknownNumRows()
Range("D5").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
- Close the VBA window.
- Go to Developer ➤ Macros.
- In the Macro dialog box, select UnknownNumRows and click Run.
- The empty cell (e.g., D8) will be highlighted.
Example 4 – Excel VBA to Loop through Specified Range until Blank Cell
This method loops through a specific range (e.g., B5:D10) to find the first empty cell.
STEPS:
- Right-click the sheet tab and select View Code.
- Paste the following code in the VBA window.
Sub LoopThruRange()
Range("B5:D10").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
- Close the VBA window.
- Go to Developer ➤ Macros.
- In the Macro dialog box, select LoopThruRange and click Run.
- The empty cell (e.g., B9) will be highlighted.
Download Practice Workbook
