Of course we can use logical statements like If statements to test properties and conditions before performing the actions. The loop starts at the first item in the collection (the first sheet in the workbook), and performs the line(s) of code between the For and Next lines for each item in the collection (every sheet in the workbook). The example below contains a For Next Loop that loops through each worksheet in the workbook and unhides each sheet. The job of the For Next Loop is to perform the same actions (lines of code) on each item in the collection. And any other object you interact with in Excel.The collection can be a collection of objects or a list of numbers.Įxamples of collections of objects include: The For Next Loop allows us to loop through a collection of items in Excel. The-For-Next-and-For-Each-Loops-Explained-for-VBA-Excel-Excel-Campus.zip Download How Does the For Next Loop Work? Download the Example Fileĭownload the free Excel file that contains examples of macros with the For Next Loop. In this article we will look at the two types of For Next Loops. The For Next Loop is the most common type of loop that helps us with these repetitive jobs. Tasks that can take hours to do manually can be completed in a matter of seconds with a loop. We can use loops in our VBA macros to repeat actions very quickly. These tasks can be extremely time consuming and BORING!įortunately, there is a way out. This could be actions like: formatting multiple ranges, unhiding multiple sheets, copying and pasting to several workbooks, apply filters to multiple tables or pivot tables, replacing values, updating formulas, etc.Ĭan you think of a few tasks where you had to repeat the same process over and over again? In Excel we spend a lot of time repeating simple tasks. Skill level: Intermediate The Power of VBA Loops This article explains how the loop works to repeat actions on a collection of items, which saves us a ton of time with our jobs.

Bottom line: The For Next Loops are some of the most powerful VBA macro coding techniques for automating common tasks in Excel.

Visual basic for excel cheat sheet