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

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

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.

Open the VBA window and Insert a 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

Code Breakdown

We start by defining a new sub-procedure called Print_Until_Empty_Cell.
We declare a variable called currentCell as a Range object and set it to the cell C5 using the Range function.
We use a Do While loop to loop through the cells in column C until an empty cell is encountered. We check if the current cell is not empty using the condition Value <> “”.
Inside the loop, we use the Print statement to print the value of the current cell to the Immediate window.
We use the Offset method to move to the next cell in the column by setting currentCell to the cell one row below the current cell using the Offset(1, 0) method.
The loop continues until an empty cell is encountered in column C.

We will see data from our worksheet column Name displayed in the immediate window until 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.

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

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

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

Code Breakdown

We use a Do While loop to loop through the cells in row 5 until an empty cell is encountered.
We check if the current cell is not empty using the condition Value <> “”.
Inside the loop, we use the IsNumeric function to check if the value of the current cell is numeric. If it is, we use the Print statement to print a message to the Immediate window that tells us the value of the cell.
We use the Offset method to move to the next cell in the row by setting currentCell to the cell one column to the right of the current cell using the Offset(0, 1) method.
The loop continues until an empty cell is encountered in row 5.

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

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

Observe we have an empty space at the end of the data of column Name.

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

Code Breakdown

We set the outputSheet variable to the worksheet named “Output” using the Worksheets function and set the outputRow variable to 5.
We set the currentCell variable to the cell C5 using the Range
We use a Do While loop to loop through the cells in column C until an empty cell is encountered. We check if the current cell is not empty using the condition Value <> “”.
Inside the loop, we use the Cells property of the outputSheet object to write the value of the current cell to the corresponding cell in column B of the outputSheet.
We use the Offset method to move to the next cell in the column by setting currentCell to the cell one row below the current cell using the Offset(1, 0) method.
We also increment the outputRow variable by 1 to move to the next row in column B of the outputSheet.
The loop continues until an empty cell is encountered in column C.

We will see those data copied to another “Output” worksheet.

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

Observe we have empty cells in column E.

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

Code Breakdown

We set rowToCheck to 5, which is the row we want to check.
We use the Cells function to determine the last column in the row by starting at cell A5 and using the End(xlToLeft) method to find the last cell with data in the row. We then assign the column number to the lastColumn variable.
We use a For Each loop to loop through the cells in the row, starting at cell A5 and ending at the last non-empty cell in the row.
Inside the loop, we use an If statement to check if the current cell is non-empty using the condition Value <> “”.
If the cell is non-empty, we use the Print statement to print the value of the current cell and its address to the Immediate window.
The loop continues until all non-empty cells in the row have been checked.

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.