how to loop through multiple sheets using VBA

Hi
I am trying  to loop this code to multiple sheets in workbook and delete particular criteria from all sheets

Sub sbDeleteRows()
'Array variable declaration
Dim listCriterias() As Variant
Dim lastRow As Integer, i As Integer
'Defien your own criterias here to remove rows
listCriterias = Array("Closed by Bank", "2nd criteria", "3rd criteria", "4th criteria")
'Find last row in column 'I'
lastRow = Sheets("Closed Calls").Cells(Sheets("Closed Calls").Rows.Count, "I").End(xlUp).Row
For lRow = lastRow To 1 Step -1
With Sheets("Closed Calls")
For i = LBound(listCriterias) To UBound(listCriterias)
If .Range("I" & lRow) = listCriterias(i) Then
.Rows(lRow).Delete
End If
Next
End With
Next
End Sub
Top Contributor Asked on May 12, 2017 in VBA.
Add Comment
1 Answer(s)

Hi,

You can loop through the Sheet using Workbook Sheets Collection. See the Updated Code below:

Sub sbDeleteRows()
'Array variable declaration
Dim listCriterias() As Variant
Dim sht
Dim lastRow As Integer, i As Integer
'Defien your own criterias here to remove rows
listCriterias = Array("Closed by Bank", "2nd criteria", "3rd criteria", "4th criteria")
'Find last row in column 'I'
For Each sht In ActiveWorkbook.Sheets ' or Thisworkbook.Sheets
With sht
lastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
For lRow = lastRow To 1 Step -1
For i = LBound(listCriterias) To UBound(listCriterias)
If .Range("I" & lRow) = listCriterias(i) Then
.Rows(lRow).Delete
End If
Next
Next
End With
Next sht
End Sub
Keymaster Answered 5 days ago.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.