Delete worksheets without loop

[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.

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

[Rick Rothstein]

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.

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...

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...

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

[Admin]

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

as always

[Excel Fox]

My only suggestion would be to use Sheets instead of WorkSheets

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

[Rick Rothstein]

My only suggestion would be to use Sheets instead of WorkSheets

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

And a good suggestion it is!

[snb]

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'

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