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. But, these Empty Cells can create problems during our further calculations. So, it’s necessary to find out those cells. Again, searching manually for the 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 to apply Excel VBA to Loop through Range until Empty Cell.
Download Practice Workbook
Download the following workbook to practice by yourself.
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.
4 Examples to Loop through Range until Empty Cell with VBA in Excel
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.
- 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.
- 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.
- How to Use VBA to Select Range from Active Cell in Excel (3 Methods)
- Excel Macro: Sort Multiple Columns with Dynamic Range (4 Methods)
- VBA to Loop through Rows and Columns in a Range in Excel (5 Examples)
- How to Convert Range to Array in Excel VBA (3 Ways)
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.
- 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.
- 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.
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.