Delete worksheets without loop

Found an interesting challenge here

Challenge is, delete all worksheets except one. I guess the one sheet would be the first one. Even if it's not the first one we can move to first.

Here is my attempt.

Code:

---

Sub kTest()
    Dim i  As Long, a
    i = Worksheets.Count
    a = Application.Transpose(Evaluate("Row(2:" & i & ")"))
    Worksheets(a).Select
    Application.DisplayAlerts = 0
    Worksheets(a).Delete
    Application.DisplayAlerts = 1
End Sub

---

Quote:

---

Originally Posted by Admin

Found an interesting challenge here

Challenge is, delete all worksheets except one. I guess the one sheet would be the first one. Even if it's not the first one we can move to first.

Here is my attempt.

Code:

---

Sub kTest()
    Dim i  As Long, a
    i = Worksheets.Count
    a = Application.Transpose(Evaluate("Row(2:" & i & ")"))
    Worksheets(a).Select
    Application.DisplayAlerts = 0
    Worksheets(a).Delete
    Application.DisplayAlerts = 1
End Sub

---

---

You can reduce all that down to 3 lines of code...

Code:

---

Sub DeleteAllButSheet1()
  Application.DisplayAlerts = False
  Worksheets(Application.Transpose(Evaluate("Row(2:" & Worksheets.Count & ")"))).Delete
  Application.DisplayAlerts = True
End Sub

---

If this code will always run alone, that is, it will not be called from within another VB code procedure, then you can reduce the macro down to 2 lines of code...

Code:

---

Sub DeleteAllButSheet1()
  Application.DisplayAlerts = False
  Worksheets(Application.Transpose(Evaluate("Row(2:" & Worksheets.Count & ")"))).Delete
End Sub

---

Quote:

---

then you can reduce the macro down to 2 lines of code...

---

:cool: as always :)

My only suggestion would be to use Sheets instead of WorkSheets

Code:

---

Sheets(Application.Transpose(Evaluate("Row(2:" & Sheets.Count & ")"))).Delete</pre>

---

Quote:

---

Originally Posted by Excel Fox

My only suggestion would be to use Sheets instead of WorkSheets

Code:

---

Sheets(Application.Transpose(Evaluate("Row(2:" & Sheets.Count & ")"))).Delete

---

---

And a good suggestion it is! :thumbsup:

And my suggestions are:

- If you use Evaluate, you can bring 'transpose' within the evaluate function.

- If you use column there's no need to 'transpose'

- if you use a named range you can use the abbreviated writing style of evaluate:

Instead of 'Select' you can use 'Delete'

Code:

---

Sub M_snb()
  Names.Add "snb_001", Columns(1).Resize(, sheets.Count - 1)
  Sheets([column(snb_001)]).Select

  Sheets(Evaluate("Column(offset(A1,,,," & sheets.Count - 1 & "))")).Select

  Sheets(Evaluate("transpose(Row(1:" & sheets.Count - 1 & "))")).Select
End Sub

---