How to Run Excel VBA Do While Loop Till Cell Is Not Empty

VBA loops are an essential tool for automating data manipulation tasks in Excel. They allow you to repeat a set of instructions multiple times, saving you time and effort on repetitive tasks. In this article, we will show you some cases to run VBA Do While Loop Till Cell Is Not Empty.

Run VBA Do While Loop Till Cell Is Not Empty in Excel: 3 Suitable Cases

In this segment, we’ll discuss 3 Suitable cases to run the Do While Loop of Excel till the cell is not empty. For the sake of understanding, we will put a task (like printing them, copying, etc.) to the data of the column till we get an empty cell. At the beginning Please follow the link to Open VBA window and insert a new module, if you don’t know it already.

1. Using VBA Do While Loop to Print Values Till Cell Is Not Empty

In the primary case, we will use the DO While Loop of Excel to print values in the immediate window of VBA. We will print the values from the worksheet till the cell is not empty in a column of the worksheet. Let’s see this simple way of using the Do While loop till the cell is not empty.

• Firstly, we prepared a dataset with Employee ID, Name, Designation We have included data for the columns and left a space in the Name column so that we can check it out with VBA code.

• Then, Open the VBA window and Insert a new module.
• In the new module, write the following code and Run

Code:

Sub Print_Untill_Empty_Cell()
Dim currentCell As Range
Set currentCell = Range("C5")
Do While currentCell.Value <> ""
' Do something with the non-empty cell
Debug.Print currentCell.Value
' Move to the next cell in the column
Set currentCell = currentCell.Offset(1, 0)
Loop
End Sub

• Immediately, we will see data from our worksheet column Name has been displayed in the immediate window till an empty cell is found in the column. Look carefully, Lenon’s name is not displayed because there’s a space before it in column C.

Read More: How to Use Excel VBA Do While Loop with Multiple Conditions

2. Checking Numeric Values with Do While Loop Till Cell Is Not Empty

Now, let’s check the numeric values of a column and display them on the immediate window until we find an empty cell in the column. Without further delay, let’s walk through the procedures.

• Firstly, look, we have some numeric numbers in the Employee ID And we kept a cell empty (B11) for testing the empty cell.

• Later, copy the following code into a new module and run it.

Code:

Sub Check_Numeric()
Dim currentCell As Range
Set currentCell = Range("B5")
Do While currentCell.Value <> ""
' Do something with the non-empty cell
If IsNumeric(currentCell.Value) Then
Debug.Print "The value in cell " & currentCell.Address & " is " & currentCell.Value
End If
' Move to the next cell in the row
Set currentCell = currentCell.Offset(1, 0)
Loop
End Sub

• After running the code, we will see the numeric values displayed until the code finds an empty cell in the column of the worksheet in the immediate window of VBA.

3. Copy Cell Values to Another Sheet with Do While Loop Until Empty Cell Is Found

Now, let’s see how to copy the values from the column of the worksheet to another worksheet till an empty cell is found. Of course, we will do it with VBA.

• Firstly, observe we have empty space at the end of the data of column Name.

• Afterward, write the following code in a new module and run it.

Code:

Sub Copy_until_NonEmptyCells()
Dim currentCell As Range
Dim outputSheet As Worksheet
Dim outputRow As Long
' Set the output worksheet and starting row
Set outputSheet = Worksheets("Output")
outputRow = 5
' Set the starting cell in the column
Set currentCell = Range("C5")
' Loop through the column until an empty cell is encountered
Do While currentCell.Value <> ""
' Write the value to the output worksheet
outputSheet.Cells(outputRow, 2).Value = currentCell.Value
' Move to the next cell in the column
Set currentCell = currentCell.Offset(1, 0)
outputRow = outputRow + 1
Loop
End Sub

• Finally, we will see those data copied to another “Output” worksheet.

How to Run VBA For Loop Till Cell Is Not Empty in Excel

So far, we have used the Do While loop but let’s discuss For Each loop till the cell is not empty. We used the For Loop to check and read data from row 5 of our worksheet until it faces an empty cell. Without further delay, let’s hover over the procedures.

• Primarily, observe we have empty cells in column E.

• Further, copy the following code in a new module and run it.

Code:

Sub For_Loop_NonEmpty_Cells()
Dim currentCell As Range
Dim lastColumn As Long
Dim rowToCheck As Long
' Set the row to check and the last column in the row
rowToCheck = 5
lastColumn = Cells(rowToCheck, Columns.Count).End(xlToLeft).Column
' Loop through the cells in the row
For Each currentCell In Range(Cells(rowToCheck, 1), Cells(rowToCheck, lastColumn))
' Check if the cell is non-empty
If currentCell.Value <> "" Then
' Do something with the non-empty cell
Debug.Print "The value in cell " & currentCell.Address & " is " & currentCell.Value
End If
Next currentCell
End Sub

• Finally, we will see the cell address and values displayed at the immediate window till an empty cell is found in the worksheet row.

Frequently Asked Questions

• What is a Do While loop in VBA?

Ans: A Do While loop is a control structure in VBA that allows you to execute a block of code repeatedly while a specific condition is true. The loop continues to run as long as the condition specified after the Do While keyword is true. Once the condition becomes false, the loop terminates and the code moves to the next commands.

• Can I use a For loop instead of a Do While loop to iterate through cells in Excel?

Ans: Yes, you can use a For loop instead of a Do While loop to iterate through cells in Excel. To do this, you would define a range of cells to loop through and then use a For Each loop to iterate through each cell in the range.

• Can I use a Do Until loop instead of a Do While loop to iterate through cells in Excel?

Ans: Yes, you can use a Do Until loop instead of a Do While loop to iterate through cells in Excel. The Do Until loop is similar to the Do While loop, except that it continues to execute as long as the condition specified after the Do Until keyword is false.

Things to Remember

• Don’t forget to clear the immediate window of VBA before running the next code.
• Remember to save the file as the xlsm file before running codes.
• Be careful about the cell references in the codes.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

By mastering the basics of VBA loops in Excel, you can automate repetitive tasks and manipulate data efficiently. Whether you’re working with small or large data sets, loops can help you streamline your workflow and increase productivity. Here, we have shown different ways to use the VBA Do While till cell is not empty. Hope, it will come in handy to you. Feel free to comment if you have any suggestions.

Related Articles

• VBA Loop Without Do Error
• How to Use VBA While Wend Statement in Excel