Existance Check Fails

[ChuckM]

I check for the existance of a worksheet in my workbook...if it exists
I want to delete it. I run through a list of sheet names and delete
them if they exist. This works.... e.g. getting a positive response
that the sheet exists. But I am also getting a positive response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name = sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n

 

[Tom Ogilvy]

Dim SheetExists As Object

For n = 6 To 1
Set SheetExists = Nothing
On Error Resume Next
Set SheetExists = Sheets(sheetname(n))
On Error GoTo 0
If Not SheetExists is Nothing Then
' delete it if it exists"
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
End If
Next n

 

[K Dales]

-----Original Message-----
I check for the existance of a worksheet in my workbook...if it exists
I want to delete it. I run through a list of sheet names and delete
them if they exist. This works.... e.g. getting a positive response
that the sheet exists. But I am also getting a positive response when
the sheet does not exist.

Dim SheetExists As Boolean
For n = 1 To 6
On Error Resume Next
SheetExists = Sheets(sheetname(n)).name = sheetname(n)
On Error GoTo 0
If SheetExists Then
' delete it if it exists"
Sheets(sheetname(n)).Select
Application.DisplayAlerts = False
Worksheets(sheetname(n)).Delete
Application.DisplayAlerts = True
Else
' do nothing
End If
Next n
.

Follow your code: let's say sheetname(1) refers to a sheet
that exists, sheetname(2) is not there...

First time through, it finds sheetname(1), sets
SheetExists to True, and deletes the sheet with the
corresponding name.

Second time through: it reaches the line "SheetExists
= ..." and errors out, since there is no Sheets(sheetname
(n)). It resumes at the next line, but SheetExists is
STILL equal to True!!! So that is why it then tries to
delete the sheet that does not exist.

A better way to do this: Instead of stepping though your
names, step through each worksheet and see if the name is
in your list:

Dim DeleteIt as Boolean, n as Integer, n1 as Integer
Dim sheetname(6) as String

' Define your sheetnames here and store in sheetname()

For n = 1 To Sheets.Count
DeleteIt = False
For n1 = 1 To 6
If Sheets(n).Name = sheetname(n1) Then DeleteIt =
True
Next n1
If DeleteIt Then
'put code here to delete Sheets(n)
End If
Next n

 

[Tom Ogilvy]

Looping 6 x sheets.count is better than failing, but not necessarily a good
way.

See my post for a way to only loop once through the list of sheets to
delete.

 

[K Dales]

Agreed, your code is more efficient, but I do hate to turn
off error checking - there can be other reasons the code
could fail. I guess it depends on how much speed and
efficiency matter - for most practical applications I
doubt the user would notice the difference in speed.

 

[Tom Ogilvy]

I understand your point, but . . .

Be hard to squeeze in any unexpected error as written:

On Error Resume Next
Set SheetExists = Sheets(sheetname(n))
On Error GoTo 0

That is a capability offered by the language; in fact I would say it is
inherent in the design. You could as easily say, I don't like to Loop or I
don't like IF statements. Just because it has the name Error in it doesn't
mean it should be avoided when it is appropriate.

 

[K Dales]

Guess I am just too "old school" (hmmm, won't reveal my
age, but the first "PC" I worked on was a Sol-20!)

 

[Tom Ogilvy]

Timex Sinclair besides mainframes.