We store many different types of data in our Excel worksheet and it’s quite normal to miss out when working with a huge amount of data. However, these Empty Cells can create problems during our further calculations. So, it’s necessary to find out those cells. Again, searching manually for empty cells is a tiresome and ineffective process. We can easily carry out the Loop operation through our data range to figure out the blank cells. In this article, we will show you simple examples of applying Excel VBA to Loop through Range until Empty Cell.
Dataset Introduction
To illustrate, we are going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company. The below dataset has some empty cells in the Salesman column. We’ll carry out the Loop operation to point out those empty cells.
Loop through Range until Empty Cell with VBA in Excel: 4 Examples
1. Excel VBA to Loop through Known Number of Range until Single Empty Cell
We may already know the number of rows in a range if the dataset is not very large. And there can be multiple blank cells in a single column. In our first example, we will use VBA to Loop through the known number of rows in column B until we find the first empty cell. Therefore, follow the steps below to perform the task.
STEPS:
- First, select the sheet and right-click on the mouse.
- Then, select View Code.
- As a result, the VBA window will pop out and a dialog box will appear.
- After that, copy the following code and paste it into the box.
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
- Subsequently, close the VBAÂ window.
- Now, from the Developer tab, select Macros.
- Consequently, the Macro dialog box will emerge.
- Here, select SingleEmpty and press Run.
- Finally, it’ll point out cell B7 which is the first empty cell.
2. VBA to Loop through Known Number of Rows until Continuous Blank Cells
In the previous example, we pointed out the first empty cell. It didn’t matter whether there was a data cell or a blank cell after that empty cell. But, in this example, we’ll point out the empty cell which is followed by another empty cell. In the sample dataset, we have empty cells at B7, B9, and B10. Here, we’ll point out cell B9. So, learn the below process for applying Excel VBA to Loop through Range until Continuous Empty Cell.
STEPS:
- Firstly, select Sheet and go to View Code after right-clicking on the mouse.
- Consequently, a dialog box will appear in the VBAÂ window.
- Then, copy the below code and paste it there.
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
- Afterward, close the VBAÂ window.
- Now, select Developer ➤ Macros.
- As a result, the Macro dialog box will pop out and select ContinuousBlankCells.
- Next, press Run.
- Another dialog box will emerge and select cell B5 in the Select First Cell box.
- Press OK.
- At last, it’ll point to the desired cell B9.
Read More: VBA to Loop through Rows of Table in Excel
3. Loop through Unknown Number of Rows with VBA until Empty Cell
However, if we have a huge dataset and we don’t know the number of rows, we can still apply the VBA to find out the blank cell. In the following dataset, we have blank cells in column D. The proposed code thinks that each cell in the column has data entry until the end. Hence, follow the process to carry out the operation.
STEPS:
- First of all, select the sheet and go to View Code.
- A dialog box will pop out in the VBAÂ window.
- Copy the following code and paste it into the box.
Sub UnknownNumRows()
     Range("D5").Select
     Do Until IsEmpty(ActiveCell)
        ActiveCell.Offset(1, 0).Select
     Loop
End Sub
- Then, close the VBAÂ window.
- After that, select Developer ➤ Macros.
- Consequently, select UnknownNumRows in the Macro name and press Run.
- Eventually, it’ll bring out cell D8 which is an empty cell.
4. Excel VBA to Loop through Specified Range until Blank Cell
In our last method, we will carry out the loop operation through a specified range. In the dataset, B5:D10 is our specified range. Therefore, learn the following steps to apply Excel VBA to Loop through Range until Empty Cell.
STEPS:
- In the beginning, go to View Code.
- Then, copy the below code and paste it into the dialog box in the VBAÂ window.
Sub LoopThruRange()
     Range("B5:D10").Select
     Do Until IsEmpty(ActiveCell)
        ActiveCell.Offset(1, 0).Select
     Loop
End Sub
- Subsequently, close the VBAÂ window.
- Now, select Macros from the Developer tab.
- As a result, the Macro dialog box will appear.
- Select LoopThruRange and press Run.
- In the end, it’ll return the empty cell B9.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to Loop through Range until Empty cell with VBA in Excel just like the above-described examples. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
