Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

## Excel VBA Loop

Let me make this statement at the very beginning itself. “If you want to master in VBA you need to complete the master the concept of loops in VBA macros”.

Excel VBA Loop is nothing but going through a range of cells, going through a range of objects to repeat the same task for the collection of cells or objects. Instead of doing the task in a separate set of line of codes by using loops we can shorten the length of the code to the least possible. In this article, we will concentrate on different types of Excel VBA loops and practical uses of those loops.

**Types of Excel VBA Loops**

### Type #1 – For Next Loop

For Next VBA loop allows us to loop through the range of cells and perform the same task for every cell specified in the loop. Here we need to tell the starting number & end number.

For example, if you want to insert serial numbers from 1 to 10 below is the traditional way of inserting serial numbers.

Sub SerialNumber ()

Range(“A1”).Value = 1

Range(“A2”).Value = 2

Range(“A3”).Value = 3

Range(“A4”).Value = 4

Range(“A5”).Value = 5

Range(“A6”).Value = 6

Range(“A7”).Value = 7

Range(“A8”).Value = 8

Range(“A9”).Value = 9

Range(“A10”).Value = 10

End Sub

Looks fine, isn’t it? But the problem is here only 10 times we need to perform this task. But imagine what if you want to insert 100 or 1000 numbers can you write the code 100 or 1000 line. It is impossible and that is where the beauty of For Next Excel VBA loop comes handy.

**Step 1: **Open macro and declare the variable **I **as **Integer.**

Sub Insert_Serial_Number ()

Dim i As Integer

End Sub

**Step 2: **Now open the **For Loop**. Here mention the start and end of the loop using the variable** i.**

**Step 3: **Now write the code what you want to perform. We need to insert numbers from 1 to 10 in A1 to A10 cell. Instead of applying the Range method apply the Cells method.

It is asking the row number since we have already declared the variable **I **as an integer at first its value is 1. So mention **I **like your row number and 1 as your column number.

4.6 (247 ratings)

Now the value of current cell value should be one, instead of mentioning the number one mention **I **as the value. Because every time loop runs **I **value will be increasing by 1.

Now press F8 key to step into one by one line. Press F8 until it reaches For loop.

This yellow color is the indication that the selected line of code is about to run. Place a cursor on the **I **it will show the value as zero.

Now press one more time F8 key and place a cursor on **I **& now the value is 1.

So the values of **I **in everywhere is now equal to 1. “**Cells (I, 1).value = I”** means **Cells (1, 1).value = 1.**

Press the F8 key and see the value in cell A1. You must see 1 in the cell A1.

Now if you press F8 key it will go back to the For loop one more time because end limit of the variable **I **is 10. This time **i **value will be 2.

Like this For Next VBA loop will run for 10 times and insert serial numbers from 1 to 10.

### Type #2 – For Each Loop

For Each loop is for looping through a collection of objects. For the next loop loops through the cells and perform the task and For Each loop loops through objects like Worksheets, Charts, Workbooks, Shapes.

Using For Each loop we can go through all the worksheets and perform some task. For example, you can loop through all the worksheets to hide & unhide worksheets.

#### For Each Loop – Example #1

If you want to hide all worksheets except the sheet which you are working on, how will you do it If you have 20 sheets in the workbook it is a time-consuming process. But with FOR EACH loop we can perform this task.

I have 5 sheets and I want to hide all the sheets except the sheet “Main” below code would do the task for me.

Sub To_Hide_All_Sheet()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

If Ws.Name <> “Main” Then

Ws.Visible = xlSheetVeryHidden

End If

Next Ws

End Sub

#### For Each Loop – Example #2

If you want to unhide all the hidden sheets then below code will do the job for you.

Sub To_UnHide_Specific_Sheet()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

Ws.Visible = xlSheetVisible

Next Ws

End Sub

#### VBA Loop Type #3 – Do While Loop

Do While VBA loop performs the task while the given condition is TRUE and once the condition becomes FALSE it will stop looping. Unlike the other two loops Do While tests the condition at the end of the loop not at the beginning of the other two VBA loops.

I will show you the example of inserting serial numbers with Do While VBA loop.

Sub Do_While_Example()

Dim i As Integer

i = 1

Do While i < 11

Cells(i, 1).Value = i

i = i + 1

Loop

End Sub

The above run the loop while **I **is less than 11 and keeps inserting the serial numbers. The moment **I **become greater than 11 it will stop looping.

#### VBA Loop Type #4 – Do Until Loop

Unlike Do While VBA loop, Do Until loop does not run while the condition is TRUE rather it loops until the condition is FALSE. For example, look at the below code.

Sub Do_Until_Example()

Dim i As Integer

i = 1

Do Until i = 11

Cells(i, 1).Value = i

i = i + 1

Loop

End Sub

The only difference between the Do While & Do Until VBA loop is the operator we specify. In Do While we mentioned running the loop while **I **is less than (<) 11 but in Do Until loop we mentioned to run the loop Until **I **is equal to (=) to 11.

### Things to Remember

- There is a more advanced Excel VBA loop example but we will cover each one of them in a separate article in detail.
- For Each loop is for object variables.
- Though Do While & Do Until looks same mentioning the condition is very important.
- Excel VBA Loops are very important concepts in VBA.

### Recommended Articles

This has been a guide to Excel VBA Loop. Here we discuss the top 4 types of VBA loops (For Next, Do While, Do Until, For Each ) with examples and practical uses. Other useful articles related to VBA can be found here –

- Break For Loop in Excel VBA
- Progress Bar in VBA
- Excel VBA UBOUND Function
- VBA Range in Excel
- VBA Msg Box
- List Box in VBA
- Arrays in VBA
- VLookup in VBA

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion