跳过一个工作表并处理其余工作表 [英] Skip one worksheet and process the remaining worksheets

问题描述

我正在使用VBA代码，该代码从所有可用的工作表中删除所有没有单词"Statement No"的行.但是无论如何，我希望它跳过第一个工作表并继续从所有其他工作表中删除行.

I was working with a vba code which remove all the rows which dont have word "Statement No" in it from all available worksheets. But anyhow i want it to skip the first worksheet and continue removing the rows from all other worksheets.

如果您可以通过以下代码为我提供帮助，以使它跳过第一个工作表.

If you can help me with the below code so that it skips the first worksheet.

Sub doit()

Application.DisplayAlerts = False

Dim r As Long, lr As Long
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

Application.DisplayAlerts = True

End Sub

谢谢大家！

推荐答案

只需检查每个工作表名称，告诉它忽略工作表即可:

Just tell it to ignore the sheet by checking each sheet name:

Sub doit()

    Application.DisplayAlerts = False

    Dim r As Long, lr As Long
    Dim sh As Worksheet

    For Each sh In Sheets
        If sh.Name <> "IgnoreThisSheet" Then
            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
        End If
    Next

    Application.DisplayAlerts = True

End Sub

您可能还想在For Each循环中将Sheets更改为WorkSheets.工作表包含行，列等.工作表可以是图表表，宏表或对话框表. http://blogs.msdn.com/b/frice/archive/2007/12/05/excel-s-worksheets-and-sheets-collection-what-s-the-difference.aspx

You may also want to change Sheets to WorkSheets in your For Each loop. A worksheet contains rows, columns, etc. A sheet can be a chart sheet, macro sheet or dialog sheet. http://blogs.msdn.com/b/frice/archive/2007/12/05/excel-s-worksheets-and-sheets-collection-what-s-the-difference.aspx

这篇关于跳过一个工作表并处理其余工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！