Results 1 to 6 of 6

Thread: Delete worksheets without loop

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    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
    Last edited by Admin; 08-14-2012 at 10:05 PM.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    then you can reduce the macro down to 2 lines of code...
    as always
    Last edited by Admin; 08-14-2012 at 10:43 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    My only suggestion would be to use Sheets instead of WorkSheets

    Code:
    Sheets(Application.Transpose(Evaluate("Row(2:" & Sheets.Count & ")"))).Delete
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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!

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    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
    Last edited by snb; 03-04-2014 at 07:45 PM.

Similar Threads

  1. Loop to two columns and Concatenate values
    By ivandgreat in forum Excel Help
    Replies: 15
    Last Post: 04-14-2013, 08:20 PM
  2. Loop Through And Delete Multiple File Types In A Folder
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 03-30-2013, 04:47 PM
  3. Loop through a folder and find word
    By k0st4din in forum Excel Help
    Replies: 7
    Last Post: 12-08-2012, 02:22 PM
  4. Speed up Loop VBA
    By PcMax in forum Excel Help
    Replies: 15
    Last Post: 04-09-2012, 04:20 PM
  5. Loop in Array
    By stanleydgromjr in forum Excel Help
    Replies: 5
    Last Post: 07-28-2011, 05:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •