在单个工作簿中循环浏览Excel工作表 [英] Loop through Excel sheets in a single workbook

问题描述

我有以下代码，我希望它删除包含某些特定文本的所有行，例如"Statement No ****" ，后跟任何文本.我想对工作簿中的每个工作表进行此操作.

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 

推荐答案

您可以迭代 Sheets 集合，如下所示.确保使用表格变量(下面的 sh ，将每次调用限定为 Range ， Cells 等)，否则Excel只会使用 active 工作表.

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

这篇关于在单个工作簿中循环浏览Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！