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

• 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

• 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

• 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

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

Related Articles

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