Check for and delete tab 1

[remeng]

Hello all!

I am trying to check for a tab to see if it exists. If it does exist I would like to delete it. If it does not, I would like to run the rest of the macro. How can I do this? I already have the sheet name to check for and the delete command, but what would be the if statement to check for it?

thanks,

Mike

 Sub Macro1()

    Sheets("k- demo inv. stock").Select
    ActiveWindow.SelectedSheets.Delete
End Sub

 

[dhookom]

This isn't the best forum for this type of question. Consider forum707.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Try something like:

Option Explicit

Sub Macro1()
Dim i As Integer

For i = 1 To Sheets.Count
    If Sheets(i).Name = "k- demo inv. stock" Then
        Sheets(i).Delete
        Exit For
    End If
Next i

End Sub

Have fun.

---- Andy

 

[remeng]

Since I have never used the For statement, maybe I don't understand this fully, but if the "k- demo inv. stock" is not located in the sheet 1 position, will this still work? What I mean is if the statement says that

 For i = 1 To Sheets.Count

sheet is say sheet 3, will it still find and delete the sheet? Also, can you show me where I would place the next set of code if the sheet does not exist? Thanks, Mike

 

[Andrzejek]

Start Excel, add as many sheets as you want, name one of them "k- demo inv. stock", place the break at the beginning of your code (I hope you know how to do that, if not - you should ), step thru your code line-by-line (use F9 key to do that) and see what will happen.

" if the sheet does not exist?"

Option Explicit

Sub Macro1()
Dim i As Integer
[blue]Dim blnSheetIsHere As Boolean[/blue]

For i = 1 To Sheets.Count
    If Sheets(i).Name = "k- demo inv. stock" Then
        Sheets(i).Delete
        [blue]blnSheetIsHere = True[/blue]
        Exit For
    End If
Next i
[blue]
If Not blnSheetIsHere Than
    MsgBox "Sheet k- demo inv. stock is not here."
End If
[/blue]
End Sub

But like dhookom said, this question belongs to the other group.

Have fun.

---- Andy

 

[dhookom]

Thanks Andy. The [F8] key is the single step in the debug mode.
Remeng,
If you don't understand how to debug code, check out faq705-7148.


Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

[ROFL2]

The [F9] key actually sets the break-point instead of stepping through the code. 'Course, I didn't remember that, b/c I usually do that with the mouse. I just tried it to see.

remeng,

As to why Andy's code works, here's what his code does:

1. The For loop looks at ALL worksheets within your workbook.
2. It moves from one worksheet to the next
3. For each worksheet, it looks at the name
4. If the name for the "current" worksheet reviewed by code is equal to the string you entered, then it deletes the sheet.
5. The code will then continue looking through additional sheets to see if another one matches (which it won't - with the current code).

Now if you have oodles of sheets in the same workbook, and want it to possibly run a little faster, you could also add in an Exit For just after the Delete line. That way, it would delete the selected sheet, and then exit the procedure.





"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

kjv1611,

Your point 5. is not true: the loop will exit (see [tt]Exit For[/tt]) after the Delete and will not look any more because I don't think you can have 2 (or more) sheets with the same name in Excel.

You wil get the mesage "Are you sure you want to Delete this?" or something like that, but you can do this to by-pass this message:
[tt]
Application.DisplayAlerts = False
... Do you magic here
Application.DisplayAlerts = True
[/tt]

dhookom - you are right about F8 F9 keys, I guess my fingers know which key to use, I don't remember....

Have fun.

---- Andy

 

[kjv1611]

oops... I meant to double-check the code before putting that #5. Sorry.
Guess I need more sleep.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

No harm done
If you work long enough with computers, eventually you start to think and act like one: in binary style. Everything is either 1 (True) or 0 (False). I guess life as politician or a lawyer is not for me….

Have fun.

---- Andy

 

[remeng]

Thanks A lot there Andy (Andrzejek) that was a perfect solution. There was a slight typo where it say than, it should say then, but other than that it was spot on! Star to you sir!

Mike

 

[TheAceMan1]

How are ya remeng . . .

I didn't want to confuse the thread with what I had. so I waited until you achieved resolution. You can directly delete a page if you use the proper syntax and take into account two things
[ol][li]Deleting a page will cause an alert to be displayed.[/li]
[li]If the page does not exist an error will occur.[/li][/ol]
Taking the above into account the following deletes "k- demo inv. stock" directly . . .

[blue]   On Error Resume Next [green]'If delete line fails ... continue.[/green]
   Application.DisplayAlerts = False [green]'Alerts Off[/green]
   ActiveWorkbook.Sheets("k- demo inv. stock").Delete
   Application.DisplayAlerts = True [green]'Alerts On[/green][/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]