Excel VBA to Loop through Range until Empty Cell (4 Examples)

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.


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.

vba loop through range until empty


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.

STEPS:

  • First, select the sheet and right-click on the mouse.
  • Then, select View Code.

Excel VBA to Loop through Known Number of Range until Single Empty Cell

  • 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

Excel VBA to Loop through Known Number of Range until Single Empty Cell

  • Subsequently, close the VBA window.
  • Now, from the Developer tab, select Macros.

Excel VBA to Loop through Known Number of Range until Single Empty Cell

  • 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.

Read More: Excel VBA to Select Used Range in Column (8 Examples)


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.

VBA to Loop through Known Number of Rows until Continuous Blank Cells

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

VBA to Loop through Known Number of Rows until Continuous Blank Cells

  • 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.

VBA to Loop through Known Number of Rows until Continuous Blank Cells

  • 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 in Range in Excel (6 Examples)


Similar Readings


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.

Loop through Unknown Number of Rows with VBA until Empty Cell

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

Loop through Unknown Number of Rows with VBA until Empty Cell

  • 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.

Read More: How to Use VBA to Count Rows in Range with Data in Excel (5 Macros)


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.

Excel VBA to Loop through Specified Range until Blank 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

Excel VBA to Loop through Specified Range until Blank Cell

  • 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.

Read More: Excel VBA: Loop Through Columns in Range (5 Examples)


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.


Related Articles

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo