CODEWITHSUNDEEP
vbaexcel
Striker
Striker

Reputation: 247

Loop through Excel sheets in a single workbook

I have the following code and I would like it to delete all rows that contain some specific text, for example, "Statement No****", followed by any text. I would like to do this for each sheet in the workbook.

My problem is that the code below is working just for active tab, not others.

Please assist me so that it automatically loops through all worksheets.

Sub doit()

    Application.DisplayAlerts = False

    Dim i As Integer
    Dim r As Long, lr As Long
    Dim x As Integer

    x = Sheets.Count

    For i = x To 1 Step -1

        lr = Cells(Rows.Count, 1).End(xlUp).row
        For r = lr To 1 Step -1
            If InStr(Cells(r, 1), "Statement No") = 0 Then Rows(r).Delete
        Next r

    Next i

    Application.DisplayAlerts = True

End Sub

Upvotes: 1

Views: 96

Answers (1)

Bond
Bond

Reputation: 16311

You can iterate the Sheets collection as shown below. Make sure that you qualify each call to Range, Cells, etc., with the sheet variable (sh, below) or else Excel will just use the active worksheet.

 Dim sh As Worksheet
 For Each sh In Sheets

    lr = sh.Cells(sh.Rows.Count, 1).End(xlUp).row
    For r = lr To 1 Step -1
        If InStr(sh.Cells(r, 1), "Statement No") = 0 Then sh.Rows(r).Delete
    Next r

 Next

Upvotes: 2

Related Questions