循环浏览 Excel 表格 [英] Loop through Excel Sheets

问题描述

我有以下代码，我希望它在工作簿的其他 25 张工作表中运行，而不是将代码重复 25 次，对于每张工作表，有没有办法让它循环?

I have the following Code and I would like it to run in 25 other sheets of the Workbook and instead of repeating the code 25 times,for each sheet is there a way to make it loop?

有人可以帮忙吗?

Sub DeleteEmptyRows()
Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long

strSearch = "ressort"

Set ws = Sheets("01,02,03")

With ws
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row




    With .Range("A1:A" & lRow)
      .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
      .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

   ActiveSheet.Range("$A$1:$P$65536").AutoFilter Field:=1

End With
End Sub

推荐答案

将处理代码包裹在一个循环中

Wrap the processing code in a loop

for each ws in thisworkbook.sheets
    ' do something on each worksheet
next

示例

Sub DeleteEmptyRows()
    Dim ws As Worksheet
    Dim strSearch As String
    Dim lRow As Long

    strSearch = "ressort"

    For Each ws In ThisWorkbook.Sheets
        If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet3") Then
            With ws
            lRow = .Range("A" & .Rows.Count).End(xlUp).Row
                With .Range("A1:A" & lRow)
                  .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
                  .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End With
                ws.Range("$A$1:$P$65536").AutoFilter Field:=1
            End With
        End If
    Next
End Sub

所以现在如果工作表名称是 Sheet1 或 Sheet2 或 Sheet3，它们将被跳过.

so now if the sheet names are Sheet1 or Sheet2 or Sheet3 they will be skipped.

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