• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      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
      
      Asked by narsing18 on May 12, 2017 in VBA.
      1 Answers
      Keymaster

      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
      
      Answered by PNRao on August 6, 2017..