The Excel Do while loop function is another great Excel function to know. The Excel Do While Loop function is used to loop through a set of defined instructions/code while a specific condition is true.
The condition to be evaluated if another loop is to be performed is listed after the While keyword. For example:
Do While (Condition is still evaluated as true)
‘Code to be executed
Now, using this function allows you to add some powerful functionality to your Excel application. Let’s assume you want to remove all the spaces in a string in the active cell in the active worksheet. You can achieve this by combining other Excel functions into your Do While loop.
Dim StringToBeProcess As String ‘String variable to process
Dim Position As Long ‘Long variable holds position of the character string found
‘For this example we are going to assume the active cell contains the following text, namely: “Excel Help is a great site!”
StringToBeProcess = ActiveCell
Position = instr(StringToBeProcess,” “)
Do While Position > 0 ‘The instr function will return the position of the first space found working from left to right
StringToBeProcess = Left(StringToBeProcess, Position - 1) & Mid(StringToBeProcess, Position + 1,Len(StringToBeProcess) - Position)
Position = InStr(StringToBeProcess, " ")
‘Update the Position variable to find the next possible space in the string
The Excel Do While loop allowed us to cycle through the string in the active cell until we have processed all the available spaces.