Deleting Sheets from Excel file using VBA

Question

I am trying to delete all unnecessary sheets from an activeWorkbook, but Sheet1 because it will be my primary sheet with initial raw data in it. When I run the following code, my sheets gets deleted, but I get this error once it reaches my last remaining sheet. Run-time error '1004': A workbook must contain at least one visible worksheet. FYI: I am writing this macro in personal macro book so i can use it for all other excel spreadsheets.

Sub deleteSheets()

Dim ws As Worksheet

   Application.DisplayAlerts = False

   For Each ws In ActiveWorkbook.Worksheets
      If Not ws Is Sheet1 Then ws.Delete
   Next ws

End Sub

There isn't always a "Sheet1" in every workbook - are you trying to keep the first sheet in the collection? — dwirony
– dwirony, Commented Mar 14, 2019 at 19:04
@dwirony yes, the first sheet in the collection. Usually it will be Sheet1 since it's the default, correct? — Kunj Patel
– Kunj Patel, Commented Mar 14, 2019 at 19:12
Not always - if you make a new workbook, add a new sheet and delete the first one, then you'll only have a Sheet2 in your workbook. — dwirony
– dwirony, Commented Mar 14, 2019 at 19:14
@dwirony Ahh- understood! so how should i go about fixing this error? assuming the name of my first sheet is "Sheet1" or the first sheet in collection? — Kunj Patel
– Kunj Patel, Commented Mar 14, 2019 at 19:17

Mathieu Guindon · Accepted Answer · 2019-03-14 20:34:38Z

2

If Not ws Is Sheet1 Then ws.Delete

The Boolean expression {object1} Is {object2} evaluates reference equality: it will be True if {object1} is {object2}.

Here ws is a worksheet in ActiveWorkbook, and Sheet1 is (presumably) a global identifier referring to the CodeName of a sheet that exists in ThisWorkbook - which may or may not be the currently active workbook.

If ThisWorkbook isn't the ActiveWorkbook, reference equality will fail for every single sheet, and that loop will attempt to delete them all, which is why you're getting that error.

Unless you want to delete sheets in ThisWorkbook, you can't use reference equality to make that check.

You can go by Index, or by name - in any case it's a good idea to ensure there's more than a single sheet in the workbook you're working with:

For Each ws In ActiveWorkbook.Worksheets
    Debug.Assert ActiveWorkbook.Worksheets.Count > 1
    If Not ws.Name = "Sheet1" Then ws.Delete
Next

Verify that the sheet you mean to delete has the expected index (1) and/or the expected name; use the Project Explorer (Ctrl+R) and the Properties (F4) toolwindows for that. Bear in mind that the (Name) property is the sheet's CodeName identifier, which you can only use/access in the VBA project of ThisWorkbook; you'll want to verify its Name property - that's the user-modifiable "tab name" of the worksheet.

edited Mar 14, 2019 at 20:34

answered Mar 14, 2019 at 19:42

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dwirony Over a year ago

Must be a long day for you Mat :) haha

Kunj Patel Over a year ago

Great explanation! thank you very much! It worked :)

AJD Over a year ago

.... and there is the difficulty in deleting an item from a collection in a loop - hence we work backwards most of the time. But the fact that M_G hasn't implies some additional insight - would it work this time, or would it skip every second sheet?

Mathieu Guindon Over a year ago

@AJD See first bullet for runtime semantics - the enumerator is only evaluated once, on entry: that's why modfying the collection as it's iterated makes no difference. Note that many other languages have different semantics for this kind of thing. Pretty sure doing that in C# throws an exception.

Tim · Accepted Answer · 2019-03-14 19:37:33Z

1

dim i as Integer
dim ws as Worksheet

For i = 2 to ActiveWorkbook.Worksheets.Count
   Sheets(2).Delete
Next

edited Mar 14, 2019 at 19:37

answered Mar 14, 2019 at 19:22

Tim

488 bronze badges

6 Comments

Kunj Patel Over a year ago

Thanks for your reply. It worked, however this deleted the first sheet i am trying keep.

Mathieu Guindon Over a year ago

@KunjPatel does Debug.Print Sheets(1).Name output the name of the sheet you're expecting to keep? Are there hidden sheets before it?

dwirony Over a year ago

You need to loop backwards since you're deleting sheets, no? For i = ActiveWorkbook.Worksheets.Count to 2 Step - 1

Mathieu Guindon Over a year ago

@dwirony oh duh, the index will shift as sheets get removed from the collection! And that is (another reason) why you iterate object collections with a For Each loop.

Kunj Patel Over a year ago

@MathieuGuindon okay so it looks like excel uses stack to create new sheets, meaning new sheets will be on top of the old one. In this case, i am trying to keep the last sheet in my collection. My apologies for the confusion.

|

Collectives™ on Stack Overflow

Deleting Sheets from Excel file using VBA

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related