# How to Continue Excel VBA For Loop (with Examples)

## How to Launch a VBA Editor in Excel

• Click on the Developer Tab from Excel Ribbon. In case you don’t have the Developer Tab in Excel Ribbon, follow this article about how to add Developer Tab in Excel Ribbon.
• Select the Visual Basic option.

• Excel will lead you to the VBA Editor Window. Click on Insert and select Module.

• In this editor, you can write your VBA code.

## Overview of the Excel VBA For Loop Statement

Syntax:

``````For counter = Start to End [Step]
Statements
Next counter``````

Example: Use For Loop to Get Even Numbers from 1 to 30

Here is a simple example of how you can use the For loop to get the Even Numbers from 1 to 30.

``````Sub Get_Even_Numbers()
Dim i As Integer
For i = 1 To 30
If i Mod 2 = 0 Then
Debug.Print i
Else
End If
Next i
End Sub``````
• Use this code in your VBA editor and Run the code. You will get even numbers from 1 to 30.

Code Breakdown

``Sub Get_Even_Numbers()``

The code defines a subroutine named “Get_Even_Numbers”.

``Dim i As Integer``

It declares a variable named “i” as an Integer.

``For i = 1 To 3 ``

It sets up a loop that will iterate from 1 to 30, with the loop index variable “i” taking on each value in that range.

`````` If i Mod 2 = 0 Then
Debug.Print i
Else
End If``````

For each value of “i” in the loop, the code checks if “i” is an even number by using the “Mod” operator to check if the remainder when “i” is divided by 2 is equal to 0. If “i” is even, it prints the value of “i” to the Debug window. If “i” is odd, the code does nothing.

``````Next i
End Sub``````

Once the loop has finished iterating through all values of “i” from 1 to 30, the subroutine ends.

## How to Continue the Excel VBA For Loop Statement: 2 Examples

Since VBA doesn’t have a continue statement to skip an iteration, you can use other statements within a For loop.

### Example 1 – Use the If Statement to Skip an Iteration and Continue to Other Iterations

Suppose you have a dataset of the marks of some students. Among the students, some were Absent during the examination. You want to get the present students’ ID, Name, and Marks. You can perform this task by simply using a For…Next Loop. But, as the Value of Marks column is Absent in 2 cells, the Iteration of For Loop may face some difficulty continuing the loop while i=9.

• Use the code below in the VBA Editor.
• Press Run.
• You get the ID, Name, and Marks of the Present Students on the Immediate window.
``````Sub For_loop_continue_use_if()
Dim i As Integer
Dim lastrow As Integer
' Find the last row in the data set
lastrow = Range("D" & Rows.count).End(xlUp).row
' Loop through each row in the data set
For i = 5 To lastrow
' Perform some operation on each row
If cells(i, 4).Value = "Absent" Then
'Do nothing
' For example, you can print the ID, Name, and Age of each person
Else
Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
cells(i, 3) & ", Marks: " & cells(i, 4)
End If
Next i
End Sub``````

Code Breakdown

``Sub For_loop_continue_use_if()``

This line starts the definition of the subroutine and sets its name.

``````Dim i As Integer
Dim lastrow As Integer``````

These 2 lines declare 2 variables as integers: i and lastrow. Here, “i” is used as a loop counter, and “lastrow” is used to store the last row number of the dataset.

``lastrow = Range("D" & Rows.count).End(xlUp).row``

This line finds the last row number of the dataset. It starts from the bottom of the worksheet column D where is the Marks data “Range(“D” & Rows.count)” and goes up until it finds the last non-empty cell “End(xlUp)”. Finally, it returns the row number of that cell “.row”.

``For i = 5 To lastrow``

In this step, a loop starts that will iterate through all rows from row 5 to the last row of the dataset.

``If cells(i, 4).Value = "Absent" Then``

This line checks the value of the cell in the fourth column of the current row “cellls(i,4).value”. If it is equal to the string “Absent”, then nothing happens.

`````` Else
Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
cells(i, 3) & ", Marks: " & cells(i, 4)``````

This step is executed if the value of the 4th column of the current row is not “Absent”. In this case, the subroutine prints a message to the immediate window that shows 2nd, 3rd, and 4th columns of the current row. This message is formatted as “ID:(value of 2nd column) , Name: (value of 3rd column), Marks: (value of 4th column)”.

`````` End If
Next i``````

This step marks the end of the If statement and the end of the For loop. It increments i by 1 and repeats the loop until it reaches the last row of the dataset.

### Example 2 – Apply On Error Resume Next Within a For Loop to Skip an Iteration If Any Cell Has an Error Value

During performing iterations, if the For loop faces an error value, the code doesn’t work. For example, we have a dataset where 2 of the cells have a #DIV/01 error. The loop will skip such error values and continue the rest of the iterations.

• Use the following code on your VBA editor.
• Run the code by pressing Alt + F5 to see the result.

``````Sub For_loop_continue_on_error()
Dim i As Integer
Dim lastrow As Integer
' Find the last row in the data set
lastrow = Range("D" & Rows.count).End(xlUp).row
' Loop through each row in the data set
For i = 5 To lastrow
On Error Resume Next
Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
cells(i, 3) & ", Marks: " & cells(i, 4)
Next i
End Sub``````

Code Breakdown

``Sub For_loop_continue_on_error()``

This line defines the subroutine named “For_loop_continue_on_error()”

``````Dim i As Integer
Dim lastrow As Integer``````

These two lines of the code declare 2 variables i and lastrow.

``lastrow = Range("D" & Rows.count).End(xlUp).row``

This line finds the last row number of the dataset. It starts from the bottom of the worksheet column D where is the Marks data “Range(“D” & Rows.count)” and goes up until it finds the last non-empty cell “End(xlUp)”. Finally, it returns the row number of that cell “.row”.

``````For i = 5 To lastrow
On Error Resume Next
Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
cells(i, 3) & ", Marks: " & cells(i, 4)
Next i``````

This block of code creates a For loop that iterates through the rows of data, starting from row 5 and ending at the last row. Within this loop, the “On Error Resume Next” statement in Excel VBA tells VBA to ignore any errors that occur in the subsequent code and continue with the next iteration of the loop. The “Debug.Print” statement prints a message to the intermediate window that includes the ID, Name, and Marks of the dataset. The underscore character at the end of the second line allows the statement to continue on the next line for readability.

This code loops through a range of data, ignoring errors and continuing to the next iteration of the loop, and prints a message to the intermediate window for each row of data.

## Some Cases Where You Can Continue Iterations in an Excel VBA For Loop

### Case 1 – Use If Statement Within a For Loop to Create Criteria Until the Loop May Continue

#### Part 1.1 Single Criteria in For Loop

If you want to skip a specific iteration and then continue the loop, you can follow this code.

``````Sub omit_single_iteration()
Dim i As Integer
Dim output As Variant
For i = 1 To 10 Step 1
If i = 6 Then
'Do Nothing
Else
output = output & vbNewLine & i
End If
Next i
Debug.Print output
End Sub``````

Code Breakdown

This code block is written in Visual Basic for Applications (VBA) and uses a loop to generate a string containing numbers from 1 to 10, skipping 6. Here’s a Code Breakdown of what’s happening.

``Dim i As Integer``

This line declares a variable named i as an Integer.

``Dim output As Variant``

This Portion declares the output variable as a Variant.

``For i = 1 To 10 Step 1``

This step sets up a loop that will run 10 times, with i starting at 1, incrementing by 1 each time, and stopping at 10.

``If i = 6 Then``

It checks if the value of i is equal to 6.

``'Do Nothing``

If i is equal to 6, the code block between this line and the End If statement will not execute. In other words, nothing will happen for i = 6.

``Else``

If i is not equal to 6, the code block following this line will execute.

``output = output & vbNewLine & i``

This line appends a new line character (vbNewLine) and the current value of i to the output string variable.

``End If``

This step ends the If statement.

``Next i``

This line signals the end of the loop and moves the loop counter (i) to the next value.

``Debug.Print output``

Finally, this line prints the value of the output variable to the immediate window in the VBA editor.

In summary, the code generates a string with numbers 1 to 10 (excluding 6) and prints the resulting string to the immediate window for debugging purposes.

Read More: Excel VBA For Loop with Array

#### Part 1.2 – Multiple Criteria Within If Statements and Or Operator

This is an example where we skipped the iteration for 3 iterations and continued the For loop for the other iterations.

``````Sub omit_multiple_iterations()
Dim i As Integer
Dim output as String
For i = 1 To 10 Step 1
'Omit multiple iterations
If i = 6 Or i = 8 Or i = 9 Then
'Do Nothing
Else
output = output & vbNewLine & i
End If
Next i
Debug.Print output
End Sub``````

Code Breakdown

This VBA code defines a subroutine called “omit_multiple_iterations”. Here’s a Code Breakdown of the code:

``````Dim i As Integer
Dim output As String``````

This portion of the code declares 2 variables: i, output as integer and string respectively. i is used for iterating through the loop. output is to store the result.

``For i = 1 To 10 Step 1``

The loop starts with the For statement, which initializes the value of i to 1 and sets the loop to run while i is less than or equal to 10. The Step 1 argument specifies that the loop should increment i by 1 each time.

`` If i = 6 Or i = 8 Or i = 9 Then``

The If statement checks whether i is equal to 6, 8, or 9. If it is, then the code does nothing, and the loop moves on to the next iteration. If i is not equal to any of those values, the code moves on to the Else clause.

In the Else clause, the code appends the value of i to the output variable. The & operator concatenates the previous value of output with a new line character and the current value of i. The result is that each value of i that is not equal to 6, 8, or 9 is added to the output variable on a new line.

After the loop has finished iterating, the code prints the value of output to the Debug window using the Debug. Print statement.

In summary, this code skips over iterations where i is 6, 8, or 9 and concatenates the remaining values of i to a string with a line break. The final output is printed to the Debug window.

### Case 2 – Skipping Values in an Array with the For Each Loop

In this code, we have a list of 5 different items. We want to print only Cheetos, Cheese, and Milk. We have used the If statement to skip iterations of other values such as Barcel and Yogurt.

``````Sub for_loop_continue_array()
Dim Groceries(1 To 5) As String
Dim concat_str As String
Dim item As Variant
Groceries(1) = "Cheetos"
Groceries(2) = "Barcel"
Groceries(3) = "Cheese"
Groceries(4) = "Yogurt"
Groceries(5) = "Milk"
'Omit values like continue does
For Each item In Groceries
If item = "Barcel" Or item = "Yogurt" Then
'Do Nothing
Else
concat_str = concat_str & item & ", "
End If
Next item
Debug.Print concat_str
End Sub``````

Code Breakdown

This VBA code defines a subroutine named for_loop_continue_array that performs the following actions:

``Dim Groceries(1 To 5) As String``

Declares an array named “Groceries” of size 5, where each element is a string. The array is populated with 5 grocery items: “Cheetos”, “Barcel”, “Cheese”, “Yogurt”, and “Milk”.

``Dim concat_ str As String``

Declares a string variable named “concat_str” that will be used to concatenate all the grocery items except “Barcel” and “Yogurt”.

``Dim item As Variant``

Declares a variant variable named “item”.

``For Each item In Groceries``

Loops through each item in the “Groceries” array using a “For Each” loop.

``````If item = "Barcel" Or item = "Yogurt" Then
'Do Nothing
Else
concat_str = concat_str & item & ", "
End If``````

For each item, it checks if it is equal to “Barcel” or “Yogurt”. If it is, then nothing happens and the loop continues to the next item. If it is not, then the item is concatenated to the “concat_str” string variable followed by a comma and a space.

``Debug.Print concat_str``

After all items have been checked, the concatenated string variable “concat_str” is printed to the debug console using the “Debug.Print” statement.

The purpose of the code is to concatenate all the grocery items in the “Groceries” array except for “Barcel” and “Yogurt” into a string, which is then printed to the debug console. The code achieves this by using a “For Each” loop and an “If” statement with a “continue” behavior to skip over the unwanted items.

## How to Continue Iterations in Other Loops in Excel VBA

### Case 1 – Continue Do Until Loop If Any Cell Has Negative value

We have a dataset where a Student gets negative marks in Literature. But, we want to calculate the total marks of the students without subtracting the negative marks from the total marks. We have skipped an iteration of Do Until loop which adds the negative marks.

``````Sub Do_until_continue_loop()
Dim ws As Worksheet
Dim total As Double
Dim i As Integer
' Set the worksheet object to the active sheet
Set ws = ActiveSheet
i = 5
' Loop through the values in column A
Do Until IsEmpty(ws.cells(i, 3))
' Check if the value is negative
If ws.cells(i, 3) < 0 Then
' Skip negative values
i = i + 1
Else
' Add the value to the total
total = total + ws.cells(i, 3)
i = i + 1
End If
Loop
' Display the total in a message box
cells(11, 3).Value = total
End Sub``````

Code Breakdown

``Sub Do_until_continue_loop()``

The code defines a subroutine called “Do_until_continue_loop”.

``````Dim ws As Worksheet
Dim total As Double
Dim i As Integer``````

It declares three variables: “ws” as a worksheet object, “total” as a double-precision floating-point number, and “i” as an integer.

``Set ws = ActiveSheet``

The variable “ws” is set to the active worksheet using the “ActiveSheet” property.

``i = 5``

The variable “i” is initialized to 5.

``Do Until IsEmpty(ws.cells(i, 3))``

The code uses a “Do Until” loop to iterate through the cells in column C, starting from row 5, until an empty cell is encountered.

`````` If ws.cells(i, 3) < 0 Then
i = i + 1``````

Inside the loop, the code checks if the current cell value is less than 0. If it is, it increments the “i” variable to skip the current row.

`````` Else
total = total + ws.cells(i, 3)
i = i + 1
End If``````

If the current cell value is greater than or equal to 0, the code adds the current cell value to the “total” variable and increments the “i” variable.

``````cells(11, 3).Value = total
End Sub``````

Once the loop finishes, the code writes the value of the “total” variable to cell C11.

### Case 2 – Continue Do While Loop If Any Cell Value is Negative

For the same dataset, we will use the Do While loop.

``````Sub CalculateTotal()
Dim ws As Worksheet
Dim total As Double
Dim i As Integer
' Set the worksheet object to the active sheet
Set ws = ActiveSheet
i = 5
' Loop through the values in column A
Do While Not IsEmpty(ws.cells(i, 3))
' Check if the value is negative
If ws.cells(i, 3) < 0 Then
' Skip negative values
i = i + 1
Else
' Add the value to the total
total = total + ws.cells(i, 3)
i = i + 1
End If
Loop
' Display the total in a message box
cells(11, 3).Value = total
End Sub``````

Code Breakdown

``Dim ws As Worksheet``

Declares a variable named ws of type Worksheet.

`` Dim total As Double``

Declare a variable named total of type Double.

`Dim i As Integer `

Declare a variable named i of type Integer and set it to 5.

``````Set ws = ActiveSheet
i = 5``````

Set the ws object to the active worksheet.

``Do While Not IsEmpty(ws.cells(i, 3))``

Begin a loop that continues until the cell in column C at the current row is empty.

`` If ws.cells(i, 3) < 0 Then``

Check if the value in column C at the current row is negative. If it is, skip to the next row.

``````Else
total = total + ws.cells(i, 3)``````

If the value in column C at the current row is non-negative, add it to the total.

`````` i = i + 1
End If``````

Increment i by 1 to move to the next row.

`cells(11, 3).Value = total`

Display the value of the total in cell C11.

## How to Exit a For Loop in Excel VBA

### Method 1 – Use the Exit For Statement to Stop Before the Mentioned Iteration

Exit For Statement works great to exit a loop.

• Follow the code mentioned below.

``````Sub for_loop_exit()
For i = 1 To 10
If i = 5 Then
Exit For
End If
Debug.Print i
Next i
End Sub``````

Code Breakdown

``Sub for_loop_exit()``

The code defines a subroutine named “For_loop_exit”.

``For i = 1 To 10``

The loop is defined using a “For” statement with a counter variable “i” that starts at 1 and goes up to 10.

``````If i = 5 Then
Exit For
End If
Debug.Print i
Next i
End Sub``````

Within the loop, an “If” statement checks whether the current value of “i” is equal to 5. If “i” is equal to 5, the “Exit For” statement is executed, which immediately exits the loop and jumps to the next statement after the “Next i” statement. If “i” is not equal to 5, the code inside the loop is executed, which in this case simply prints the value of “i” to the immediate window. Once the loop has been completed, the subroutine ends.

Read More: Excel VBA: How to Exit a For Loop

### Method 2 – Apply the GoTo Statement to Stop a For Loop After a Specific Iteration

The VBA “goto” statement is a control structure used to transfer control to a specific line or label within a VBA procedure. It is often used to create more complex flow control structures or to handle errors and exceptions.

``````Sub jump_using_goto()
Dim i As Integer
For i = 1 To 10
If i = 5 Then
GoTo Line1
End If
Debug.Print "The value is " & i
Next i
Line1:
End Sub``````

Code Breakdown

• ``````Sub jump_using_goto()
Dim i As Integer``````

The subroutine starts with a variable declaration i as an integer.

• ``For i = 1 To 10``

A For loop is used to iterate from 1 to 10, and for each iteration, the value of i is printed to the debug window using the Debug.Print statement.

• ``````If i = 5 Then
GoTo Line1
End If
Debug.Print "The value is " & i
Next i
Line1:
End Sub``````

The If statement checks whether the current value of i is equal to 5. If it is, then the program jumps to the Line1 label using the GoTo statement. If i is not equal to 5, then the program continues to execute the Debug.Print statement. The Line1 label is defined at the end of the subroutine and serves as a target for the GoTo statement. When the program reaches the Line1 label, it continues executing from that point until the end of the subroutine.

### Method 3 – Calculate Sales Data Using the For Loop Continue of Excel VBA

This is a dataset of Sales of a company. To calculate the total sales, we have used For loop. But, some products were not available to purchase. When the For loop enters into such a counter, it can’t do the mathematical operation and an error occurs. If the value of the Quantity column is “Not Available,” the loop skips this cell and enters the next cell.

• Use the following code:

``````Sub For_loop_example1_sales()
Dim lastrow As Long
Dim i As Long
Dim quantity As Double
Dim price As Double
Dim totalSales As Double
lastrow = ActiveSheet.ListObjects("Table1").Range.Rows.count
For i = 2 To lastrow
If ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value = "Not Available" Then
Else
quantity = ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value
price = ActiveSheet.ListObjects("Table1").Range.cells(i, 3).Value
totalSales = quantity * price
ActiveSheet.ListObjects("Table1").Range.cells(i, 4).Value = totalSales
End If
Next i
End Sub``````

Code Breakdown

This VBA code calculates the total sales for each row in a table named “Table1” and stores the result in a new column.

Here is a Code Breakdown of the code:

``Dim lastrow As Long ``

This line declares a variable named “lastrow” as a Long data type. This variable will be used to store the last row number of the “Table1” table.

``Dim i As Long ``

This portion declares a variable named “i” as a Long data type. This variable will be used as a loop counter to iterate through each row in the “Table1” table.

``Dim quantity As Double  ``

It declares a variable named “quantity” as a Double data type. This variable will be used to store the quantity value from each row in the “Table1” table.

``Dim price As Double ``

This line declares a variable named “price” as a Double data type. This variable will be used to store the price value from each row in the “Table1” table.

``Dim totalSales As Double -``

Here, this part of the code declares a variable named “totalSales” as a Double data type. This variable will be used to store the result of the quantity multiplied by price calculation.

``lastrow = ActiveSheet.ListObjects("Table1").Range.Rows.count ``

This step retrieves the total number of rows in the “Table1” table and assigns it to the “lastrow” variable.

``For i = 2 To lastrow ``

It starts a loop that will iterate through each row in the “Table1” table, starting from row 2 and ending at the “lastrow” value.

``If ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value = "Not Available" Then``

This line checks if the value in column 2 (quantity) for the current row is “Not Available”.

``quantity = ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value ``

If the value in column 2 is not “Not Available”, assign the value in column 2 to the “quantity” variable.

``price = ActiveSheet.ListObjects("Table1").Range.cells(i, 3).Value ``

This step assigns the value in column 3 (price) to the “price” variable.

``totalSales = quantity * price ``

It calculates the total sales for the current row by multiplying the “quantity” and “price” variables.

``ActiveSheet.ListObjects("Table1").Range.cells(i, 4).Value = totalSales ``

This part of the code stores the calculated total sales value in a new column (column 4) in the current row.

``End If ``

It ends the If statement.

``Next i ``

Moves to the next row in the “Table1” table.

## Difference Between For and For Each Loops in Excel VBA

The main difference between the two loops is the way they iterate through a collection or array.

The For loop is used to iterate through a collection or array by index. It has the following syntax:

``````For counter = start To end [Step stepvalue]
[statements to be executed inside the loop]
Next [counter]``````

In this loop, the counter variable is incremented by the step value (which defaults to 1 if not specified) for each iteration until it reaches the end value. The loop then ends, and the code execution continues with the next statement after the Next keyword.

On the other hand, the For Each loop is used to iterate through a collection or array by value. It has the following syntax:

``````For Each element In group
[ statements to be executed inside the loop]
Next [element]``````

The element variable is assigned to each element in the group collection or array, one by one, until all elements have been processed. The loop then ends, and the code execution continues with the next statement after the Next keyword.

Here is an example of both loops:

``````Sub For_vs_For_Each_loop()
Dim arr(1 To 5) As Integer
Dim i As Integer
For i = 1 To 5
arr(i) = i * 2
Next i
Dim item As Variant
For Each item In arr
Debug.Print item
Next item
End Sub``````

Code Breakdown

``Sub For_vs_For_Each_loop()``

Defines a new subroutine named “For_vs_For_Each_loop”

``Dim arr(1 To 5) As Integer``

Declares an array variable “arr” of integer data type with 5 elements using “Dim” keyword

``Dim i As Integer``

Declares an integer variable “i” using “Dim” keyword

``For i = 1 To 5``

Starts a “For” loop with “i” variable initialized to 1 and ending at 5

`````` arr(i) = i * 2
Next i``````

In each iteration of the “For” loop, assign the value of “i” multiplied by 2 to the “arr” array element with the same index as “i”. Ends the “For” loop with “Next i” statement.

``Dim item As Variant``

Declares a “Variant” data type variable named “item” using “Dim” keyword

``For Each item In arr``

Starts a “For Each” loop with “item” variable assigned to each element in the “arr” array

``````Debug.Print item
Next item
End Sub``````

In each iteration of the “For Each” loop, prints the value of the “item” variable to the “Immediate Window” using the “Debug.Print” statement. Then, ends the “For Each” loop with “Next item” statement. Finally, ends the subroutine with “End Sub” statement.

Some Examples of For Each Loop in Excel VBA

For Each loop are a fundamental concept in VBA programming that allows developers to loop through each element in an array, collection, or range. In this section, I have given 2 examples of using For Each loop which will give you a better understanding of For Each loop.

Example 1 – Apply a For Each Loop to Enter the Same Values in a Column

We used For Each loop to write the same value in 5 cells of a column.

``````Sub For_each_loop_example()
Dim cell As Range
For Each cell In Range("B4:B8")
cell.Value = "Hello World!"
Next cell
End Sub``````

Code Breakdown

``Sub For_each_loop_example()``

The Sub statement indicates the beginning of a subroutine, which is a block of code that performs a specific task. The name of the subroutine is “For_each_loop_example”.

`` Dim cell As Range``

The “Dim” statement declares a variable named “cell” as a Range data type. This variable will be used to refer to each cell in the specified range.

``For Each cell In Range("B4:B8")``
``````  cell.Value = "Hello World!"
Next cell``````

The “For Each” loop is used to iterate through each cell in the range “B4:B8”. “Range(“B4:B8″)” specifies the range of cells that the loop will iterate through. “cell” is the loop variable that refers to each cell in the range as the loop iterates. The statement “cell.Value = “Hello World!”” sets the value of the current cell to the text string “Hello World!”. The “Next” statement indicates the end of the loop and moves the loop to the next cell in the range.

``End Sub``

End Sub indicates the end of the subroutine.

Example 2 – Use the For Loop to Count Sheets of a Workbook

By using For Each loop, you can count the total number of datasheets of a workbook. This code can be used widely if there are so many sheets in a workbook.

``````Sub For_each_loop_CountSheets()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Dim count As Integer
For Each ws In wb.Worksheets
count = count + 1
Next ws
Debug.Print "The workbook has " & count & " worksheets."
End Sub``````

Code Breakdown

``````Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Dim count As Integer``````

The Dim statement is used to declare three variables. wb, which is declared as a Workbook object. ws, which is declared as a Worksheet object. count, which is declared as an Integer. The Set statement is used to assign the value of ActiveWorkbook to the variable wb.

``````For Each ws In wb.Worksheets
count = count + 1
Next ws
Debug.Print "The workbook has " & count & " worksheets."``````

We use the For Each loop to iterate through each worksheet in the workbook, with ws will be assigned to each worksheet in turn. Within the loop, the value of the count will increment by 1. Once the loop has finished iterating through all worksheets, the Debug.Print statement will print a message to the Immediate window indicating how many worksheets were counted. The message includes the value of the count variable, which will represent the total number of worksheets in the workbook.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Mahfuza Anika Era

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF