Using Loops and Conditions in Visual Basic

The video guide introduces For loops, Do loops, If and Goto statements. They can be used in various combinations to automatically process large data sets.

The reason to use loops and conditions is to tell Excel which section of code you want to run next. If you want to process thousands of rows of data, you may not want to treat every row identically, but you will want the same logic to be applied repeatedly.

Sometimes you have the option of getting Excel to do the heavy lifting using a combination of formulas, filters and pastes. Instead of writing IF statements in your code, you can identify the rows you want using IF formulas directly on your spreadsheet. Instead of looping through every row of your data, you can just write the same formula to every row of your spreadsheet.

We will take an in-depth look into the various methods along with their limitations. We also consider the ELSE statement as a helpful counterpart to IF. A summary is listed in the table below:

Method	Used for	Limitations
For loops	Repeating a section of code a known number of times	Number of times must be known in advance
Do While loops	Repeating a section of code only while necessary	Messy input data can cause rows to be skipped
Do Until loops	Running a section of code until a condition is met	Can be inferior to a For loop and less intuitive than a Do While loop
IF conditions	Selectively running passages of code	Suitability of complex conditions may be difficult to verify
ELSE/ELSEIF conditions	Efficiency of code and ease of reading	Multiple ELSEIF conditions need to be interpreted
IF formulas for bulk processing	Reducing code to known Excel functions and taking advantage of Excel’s speed	Only works for Excel processes that take a small number of steps

When do you use a For/Next Loop in VBA?

A For/Next loop is best for running the same process across all rows of an Excel dataset of known size. If you want process the first 10 rows of an Excel spreadsheet, you can start a loop with a line of the form ‘For CurrentRow = 1 to 10’ and closed with a line of the form ‘Next CurrentRow’. Within the loop, coded references of the form Cells(CurrentRow, SomeColumn) will look at each row of data in turn. This lets you to perform the same operation on every row of your spreadsheet.

Note that you do not have to enter fixed numbers at the start of your For loop. You can define the first and last rows dynamically by using named ranges e.g. ‘For CurrentRow = Sheet1.Range(“HeaderRow”).Row to Sheet1.Range(“LastRow”).Row’. However, we cannot change the rows we want to process once the loop has started. That can be awkward if the code is supposed to insert or delete rows based on the data it finds.

If your macro is supposed to insert rows then you can ensure all the data gets processed by starting at the bottom of your table and working your way back to the top. This would take the form ‘For CurrentRow = 10 to 1 Step -1’.

You can use For loops in conjunction with If/Goto statements to let them be exited early. This is best used to handle exceptional circumstances such as if you are transferring data to another sheet and have hit a capacity limit. This assumes the exit criteria is such that you don’t just want to resume the code immediately after the loop, otherwise you can use the ‘Exit For’ command. If early exits are going to be triggered frequently, it may be better to use a Do loop where the exit condition is built into the loop’s design.

When do you use a Do While Loop in VBA?

A Do/While loop is best for running a process from a known start point to an uncertain end point. An example would be the processing of daily sales where the number of sales varies from day to day. It is best for handling data from third party software, especially where the raw data comes across in a text format like a CSV file. If the data is inconsistently formatted, it is possible the exit condition will be hit early, and not all data will get processed.

The most frequent use for a Do While loop is to process data one row at a time until the end of the data is reached. If the end of the data is the first line with no entry in column A then you can open the loop with the line Do While Cells(CurrentRow, 1) <> "" before closing it with the two lines CurrentRow = CurrentRow + 1, then Loop. This relies on setting CurrentRow to the first row of data, typically 1 or 2 if processing a text file.

The problem with Do loops is that the condition simply looks for a blank entry in column A and does not take account of whether the data continues in the next row. Our consultants find that when data has been manually input, this is not a reliable method. Sometimes users find rows in the middle of data, clear out the data and then hide them. So a column can look like it doesn’t contain blanks even when it does.

In the example given, a Do Until loop could have been used, such as Do Until Cells(CurrentRow, 1) = "". Indeed it’s more natural to loop because something is the case than something isn’t. In practice, most datasets have a column containing data in a single identifiable format, such as amounts, dates, or internal codes. If you sort by a column of interest, you can end up with a very natural loop such as Do While Cells(CurrentRow, 1) = VBA.Date, if you only want to process today’s sales data.

When using a Do loop, you must ensure the final condition will be met at some point otherwise your code will try to run forever. Although it’s generally possible to escape out of such infinite loops, sometimes other Excel processes interfere and force you to quit Excel losing any unsaved work. For that reason, it is always worth saving an Excel workbook before testing a Do loop.

When do you use a Do Until Loop in VBA?

A Do Until loop is analogous to a Do While loop in that it can run a process from a known start point to an uncertain end point. If running through rows of a spreadsheet, a Do Until loop will run until a condition is met. A Do Until loop with a condition that is the exact opposite of an equivalent Do While loop will behave identically. For example, Do Until Count >= 100 is identical to Do While Count < 100.

Although a matter of taste, it is conceptually easier to think of the properties of the data you do want to process, as opposed to the data (or often blank rows) you don’t want to process. That means it is normally easier to write the conditions for a Do While loop. However, historic databases often concluded with a distinct END row. Then it is natural to write a Do Until loop such as Do Until Cells(CurrentRow, 1) = “END”.

Do Until loops make sense when you are not planning to process a full dataset, such as when creating top 10s. If you wanted to identify your largest orders meeting certain conditions, you would sort the raw data from largest to smallest and run through the data until you had found 10 orders meeting your criteria. Note the scenario description includes the word ‘until’, which is a good indication a Do Until loop makes sense.

Even so, you have to be confident your dataset will always contain 10 orders, otherwise you will end up stuck in an infinite loop. If you are unsure, then it is better to use a For loop that you exit with an IF statement after 10 orders have been found.

When do you use IF statements in Visual Basic macros?

IF statements are a fundamental part of any programming language as they are the simplest way to selectively process data. In VBA, writing a statement of the form ‘If <a condition is met> Then’ will ensure the subsequent code only runs if the condition is met. If the code that follows extends over multiple lines, the condition applies to all code up to a separate ‘End If’ statement.

By default, IF statements result in code being skipped when conditions aren’t met. However, if you write a line of the form ‘If <a condition is met> Then Goto SomewhereElse’, then the IF statement can be used to navigate the code when conditions are met. If you write a line of code of the form ‘SomewhereElse:’, it will serve as a marker from which Excel should continue processing the code.

IF statements in Visual Basic behave in a very similar way to IF formulas on an Excel worksheet. This means you combine them with AND and OR operators to define multiple conditions. Arguably it’s more intuitive in Visual Basic because you can write expressions of the form ‘If WeatherIsWarm = True And Rainfall = 0 Then’ instead of having to place the AND function in front of the tests.

If you want to write complex conditions then, as with an Excel formula, it can be hard to work out what’s going on. You can always step through Visual Basic code line-by-line to see whether the code is navigating your IF statements in the expected manner. The difficulty comes when using IF statements to catch unexpected data that would otherwise cause your code to error. Even if you can imagine scenarios in which your code would error, you should only try to handle such scenarios with IF statements if you are able to test them rigorously.

For error handling, our experts would recommend using the special On Error command. If you are writing complex IF statements for other reasons, it can make sense to break your statement into multiple IF statements. If you tab your code as demonstrated in the video, you should be able to keep track of which conditions to any given section of code.

When do you use ELSE/ELSEIF statements in Visual Basic macros?

ELSE statements let you take some sort of action when a condition doesn’t apply as well as when it applies. An ELSEIF statement lets you define any number of mutually exclusive conditions on which an action should be taken, although Select Case may be better in some circumstances.

A good use for an ELSE statement is if you are using a loop to bulk process data. If you can process the data, then you follow certain steps. Else you might need to highlight the data for review. Note that if you just wanted to highlight non-conforming data for review and had no steps to follow before the ‘Else’, then you could have rewritten your initial IF statement as ‘If Not <data can be processed> Then’, which would mean the Else statement was no longer required. The alternative to an ELSE statement would be to write back-to-back IF statements with mutually exclusive conditions which introduces an unnecessary source of error.

The use of ELSEIF is more nuanced. Imagine an expression of the form ‘If Country = “USA” Then <dosomething>, ElseIf Country = “Canada” Then <dosomethingelse>, ElseIf Country = “UK” <doanotherthing> Then, End If’. Here, you have to interpret all the conditions to work out when no action will be taken or, if you inserted an ELSE statement at the end, when that code would run. If your conditions are more complex, it is easy to leave gaps.

Then, although less intuitive, the same section of code could be rewritten in a section headed ‘Select Case Country’, followed by a Case list of countries alongside the actions to be taken. The benefit of ‘Select Case Country’ is that what the code that runs next can only be dependent on the country, whereas our ELSEIF statement might branch off into a completely different set of conditions.

Having said that, if you wanted to write conditions and code of the form ‘If Country = “UK” Then, ElseIf Country = “France” Then, ElseIf Continent = “Europe” Then’ then you have no choice but to use ELSEIF. Also, if you only write code for personal use then ELSEIF is easier to use and to remember.

When can Excel formulas and functions replace loops in Visual Basic macros?

Using a combination of formulas, copies, pastes, sorts and filters, you can often edit cells in bulk. Replicating the same steps within your macros can streamline your code whilst making it run faster because editing individual cells is computationally expensive.

Once you can use loops and conditions confidently, it can be tempting to apply all logic to individual cells using IF statements. Imagine processing a list of invoices to identify those outstanding. You want to delete those that have been paid and highlight those that are past due. Excel takes longer to process the deletion of a row from a spreadsheet than putting a value in a cell. The same applies when you apply a background color to a cell. Although there are programmatic tricks to improve performance, such as setting ScreenUpdating to False, it takes a long time to delete thousands of rows.

If you were tackling the problem manually, you would filter the data by a column containing the invoice status. If one didn’t exist, you would create it. You could then sort the data by status, delete everything paid, then color everything overdue. Most of those steps can be rewritten as a single line of code and, to identify the ranges being processed, you could use MATCH formulas to identify which rows contain which status.

For a large dataset, the above approach will run faster. If your conditions for processing different rows are more complicated, it may be that copying the logic to every row in formula form is faster than getting Excel to apply the logic row by row.

Another advantage of applying logic through formulas not code is that they are easier to maintain as formulas are more accessible to non-programmers.

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed September 2026.