VBA循环工作表:如果单元格不包含行，则删除行
问题描述：
我试图遍历excel工作表以删除不包含"ALTW.ARNOLD_1"或"DECO.FERMI2"的行.我已经汇编了以下在单个工作表上工作的代码，但是当我循环时，循环仅遍历所有工作表，而仅在第一工作表上执行行删除.工作表编号为 1 到 365.代码如下:
I am trying to loop over excel worksheets to delete rows not containing "ALTW.ARNOLD_1" or "DECO.FERMI2". I have assembled the following code which works on a single worksheet, but when I loop the loop just runs through all sheets while only performing the row deletion on the first sheet. The sheets are numbered 1 through 365. Here's the code:
Sub Delete_Rows()
For x = 1 To 365
Sheets(x).Select
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A1:A5000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) <> "ALTW.ARNOLD_1" And (cell.Value) <> "DECO.FERMI2" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
Next x
End Sub
答
更安全，完全避免选择/激活.您还没有在每次循环后重置 del ...
Much safer to avoid the select/activate altogether. You also were not resetting del after each loop...
Sub Delete_Rows()
Dim rng As Range, cell As Range, del As Range
dim sht as Worksheet
For x = 1 To 365
set sht=Sheets(x)
set del=Nothing 'you missed this
Set rng = Intersect(sht.Range("A1:A5000"), sht.UsedRange)
For Each cell In rng.Cells
If (cell.Value) <> "ALTW.ARNOLD_1" And (cell.Value) <> "DECO.FERMI2" _
Then
If del Is Nothing Then
Set del = cell
Else
Set del = Union(del, cell)
End If
End If
Next cell
If not del is nothing then del.EntireRow.Delete
Next x
End Sub