循环通过Excel表 [英] Loop through Excel Sheets
本文介绍了循环通过Excel表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下代码,我希望它可以在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
推荐答案
在本工作簿中为每个ws包装处理代码
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屋!
查看全文