我正在尝试删除工作簿中的所有工作表,但三个工作表,使用"and"到"if...然后"语句正在删除其中一个。我写的如下:
Sub delete()
For Each Sheet In Application.Worksheets
Application.DisplayAlerts = False
If (Sheet.Name <> "Close Price" And Sheet.Name <> "Parameters" And Sheet.Name <> "Stock") Then
Sheet.delete
End If
Next Sheet
Application.DisplayAlerts = True
End Sub
我还尝试了以下方法:
Sub delete()
For Each Sheet In Application.Worksheets
Application.DisplayAlerts = False
If (Sheet.Name <> "Close Price" And Sheet.Name <> "Parameters") Then
If (Sheet.Name <> "Stock") Then
Sheet.delete
End If
End If
Next Sheet
Application.DisplayAlerts = True
End Sub
但也不起作用。它尊重"收盘价"和"参数"工作表,但不尊重"股票"工作表。
你知道我该怎么解决吗?
非常感谢,一切顺利。grd_aa
可以使用工作表名称数组一次删除工作表。在For-Each
循环中,将要删除的工作表的名称收集到数组中。然后只需调用Worksheets(sheetsToDeteleArrayHere).delete
,即可立即删除数组中的所有工作表。呵呵
注意:使用修剪(工作表名称)可以确保前导和/或结束空格不是比较的一部分。
Sub delete()
Dim sheet
Dim sheetsToDetele
Application.DisplayAlerts = False
For Each sheet In Application.Worksheets
If Trim(sheet.name) = "Close Price" Then GoTo continue
If Trim(sheet.name) = "Parameters" Then GoTo continue
If Trim(sheet.name) = "Stock" Then GoTo continue
If IsArray(sheetsToDetele) Then
ReDim Preserve sheetsToDetele(1 To UBound(sheetsToDetele) + 1)
Else
ReDim sheetsToDetele(1 To 1)
End If
sheetsToDetele(UBound(sheetsToDetele)) = sheet.name
continue:
Next sheet
If Not IsArray(sheetsToDetele) Then _
Exit Sub
Application.Worksheets(sheetsToDetele).delete
Application.DisplayAlerts = True
End Sub
正如您所说,if语句不尊重第三个and
,我会将其更改为Select Case
,因为仅当名称与案例语句
中列出的任何其他名称不匹配时才删除(我知道我可以把所有的名字放在一行上
Case "Close Price", "Parameters", "Stock"
我只是觉得这样看起来更整洁:)
Sub delete()
Application.DisplayAlerts = False
For Each Sheet In Application.Worksheets
Select Case Sheet.Name
Case "Close Price"
Case "Parameters"
Case "Stock"
Case Else
Sheet.delete
End Select
Next Sheet
Application.DisplayAlerts = True
End Sub
问题是当您删除sheet
时,您将枚举更改为循环无法继续的点,因为完整性已受到损害。
相反,当您需要执行此操作时,请使用基于索引的循环,然后向后工作。这样,当您删除工作表时,下一个迭代不会受到影响。
Sub delete()
Application.DisplayAlerts = False
Dim i As Integer
' Work backwards so if/when a sheet is deleted,
' the indexes are not changed
For i = Sheets.Count To 1 Step -1
If Sheets(i).Name <> "Close Price" _
And Sheets(i).Name <> "Parameters" _
And Sheets(i).Name <> "Stock" _
Then
Sheets(i).Delete
End If
Next
Application.DisplayAlerts = True
End Sub