我有以下代码,我希望它在25个其他工作簿中运行,而不是重复代码25次,因为每个工作表有没有办法让它循环?
有人可以帮忙吗?
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
小智.. 11
将处理代码包装在一个循环中
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,它们将被跳过.
将处理代码包装在一个循环中
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,它们将被跳过.