For each loop - How to use For each loop Statement in Excel VBA

For Each Statement.

A For Each Loop is used to loop through each element is a Collection, or an Array. A Collection is an object that contains a set of related objects. An Array is a set of sequentially indexed elements having the same intrinsic data type. To stop an endless loop, press ESC or CTRL+BREAK

Introduction.

OK let’s see I have given few Examples’.

Option Explicit

Sub foreachloop()

Dim i As Long

For i = 1 To 10

Cells(i, 1).Value = i

Next

End Sub

Out Put.

Explanation:  for i Nothing but Range so for i = 1 to 10 targeted cells i have given cells([VBA output value], Column Number]) value = 1 to 10

Note : For statement always End with Next before End Sub.

2.Using For with IF else statement more fun and very helpful while completing repeated tasks in Excel..

In this Example i will show you Fast Way to Find matching Value’s or Name’s and Delete Excel Rows.

Place a Command Button and add following code and in Excel add some value’s as below image.

Sub foreachloop()

Dim i As Long

Dim datarng As Long

With Worksheets("sheet1")

datarng = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

For i = datarng To 1 Step -1

If Cells(i, 1).Value = "b" Then

Rows(i).Delete

End If

Next

End Sub

Note: Above code If Cells(i, 1).Value = "b" Then  Change the value as you want to Find.

Output.Click the VBA button and see Excel Vba Deleted all the "b" in "A column.

Explanation: As we Defined VB to find b in a column of "A" and delete matching rows,

Below is the Video tutorial for above example.

Do like this Page, ? Please give your Email id Sign up for Daily News Letter...