Check if a sheet exists & if so, delete it

Topic

New Reply

I have an Excel macro which, among other things, creates & names a new sheet. I am trying to “bullet-proof” the macro now, and I want to add some code at the beginning that will check for the sheet that the macro creates, and if found, delete it. Once deleted, or if not found, it should go on and run the macro. Any ideas on how to do this? Thanks in advance.

Reply | Quote

Replies

Never mind, a co worker helped me out with this. For the curious, here’s what we used:

On Error GoTo errorhandler
Sheets(“Oursheet”).Select
errorhandler:
If Err.Number = 9 Then
Sheet.Activate (“Sheet2”)
Sheets.Add.Name = “Oursheet”
End If
(rest of macro)…

Reply | Quote

Hi,

I’m not fond of solutions using the error handler, so if there’s another way of doing something I will (the errorhandler may get activated for the wrong reason; I’ve sometimes had it not kicking in at all.

So my solution would be soemthing like;

Dim i As Integer

For i = ActiveWorkbook.Sheets.Count To 1 Step -1
  If ActiveWorkbook.Sheets(i).Name = "OurSheet" Then
    ...
  End If
Next

which gets rid of the need for an errorhandler.

The reason for using a “for i =” look rather than a “for each” loop, and doing it backward, is that if we are in a forward loop and delete a sheet, things may not get handled correctly.

Reply | Quote

I would do something like this:

Dim wsOS As Worksheet

    On Error Resume Next
    Set wsOS = Worksheets("OurSheet")
    On Error GoTo 0
    If Not wsOS Is Nothing Then
        Application.DisplayAlerts = False
        wsOS.Delete
        Application.DisplayAlerts = True
    End If
    wsOS = Nothing

Reply | Quote