VBA - adjust to not delete all sheets

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239
Hi guys

i have hte following line of code that deletes all sheets that aren't named "Main".
Code:
   For I = ActiveWorkbook.Worksheets.Count To 1 Step -1
      On Error Resume Next
      If Worksheets(I).Name <> "Main" Then _
         Worksheets(I).Delete
On Error Resume Next
   Next I

But, i'd like a couple of other sheets to not be deleted too. such as "Main 2" etc. Is it possible to adjust this code so that it won't delete any sheet named in an array or any sheet that contains "Main" in its name?

Thanks for your help

John
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi John

If you mean that those names start by Main, use

Code:
If Left(Worksheets(I).Name,4) <> "Main" Then _

HTH
PGC
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
John

You could test for Main being in the name like this.
Code:
For Each ws In Worksheets
     If InStr(ws.Name, "Main")= 0 Then ws.Delete
Next ws
If you want to not delete other sheets you might want to look at a Select Case structure.
Code:
For Each ws In Worksheets
     Select Case ws.Name
         Case "Main", "Main 2", "Some other sheet"
               ' do nothing
         Case
               ' ws.Delete
     End Select
Next ws
 

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239

ADVERTISEMENT

Fantastic, what a neat idea. Thanks for your swift help.

Cheers

john
 

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239

ADVERTISEMENT

norie - how does that code work?

i've got it to work, just odn't understand how it's doing it!

Code:
For Each ws In Worksheets
     Select Case ws.Name
         Case "Main", "Main 2", "Some other sheet"
               ' do nothing
         Case ws.Delete
     End Select
Next ws
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
John

There's actually some typos in it, I just typed it here not in the VBE.:oops:

Bad habit. o_O

Should be this really.
Code:
For Each ws In Worksheets
     Select Case ws.Name
         Case "Main", "Main 2", "Some other sheet"
               ' do nothing
         Case Else
                ws.Delete
     End Select
Next ws
What it does is loop through the worksheets, checks the names of the sheets against the first Case and if the name matches does nothing, otherwise it deletes the sheet.
 

Forum statistics

Threads
1,136,612
Messages
5,676,800
Members
419,651
Latest member
alexanderguhr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top