Delete worksheets without loop

[Admin]

Found an interesting challenge here (http://excelpoweruser.wordpress.com/2012/08/14/excel-vba-challenge/comment-page-1/)

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 (http://excelpoweruser.wordpress.com/2012/08/14/excel-vba-challenge/)

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

:cool: as always :)

[Excel Fox]

My only suggestion would be to use Sheets instead of WorkSheets



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

[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! :thumbsup:

[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