Delete all sheets in between two sheets

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113
Dear Sir/Madam,
Have a nice day!

Problem: I needed to delete sheets in between two sheets.

What have I tried yet: After looking in Internet I have found a code in this forum which does the exactly same.

Link : https://www.mrexcel.com/forum/excel-questions/762960-delete-all-sheets-between-sheets-x-y.html

Code is:

Code:
Sub DeleteSheetsBetweenFirstAndLastMarkerSheets()
  Dim X As Long
  Application.DisplayAlerts = False
  For X = Sheets("Last_Sheet").Index - 1 To Sheets("First_Sheet").Index + 1 Step -1
    Sheets(X).Delete
  Next
  Application.DisplayAlerts = True
End Sub

This works perfectly. But after running this code I am getting a wired problem.

Problem:

Symptom 1: After deleting my sheets between "First_Sheet" and "Last_Sheet", when I clicked 'Save' or do 'Ctrl+S' (Normal saving workbook), my excel crashes every time.

Symptom 2: After running this code also found that on all shapes on my "Last_Sheet" (I am using these shapes as button to run macro. Each shapes assigned to a macro) got deactivated, means on which I am unable to click.


Try no 2:
Now I have changed the code to see if 'Delete Sheet' somehow creating the problem. I wrote the simple sheet array code to delete 5 sheets in between"First_Sheet" and "Last_Sheet"

Code:
Sub deleteSheets()


Application.DisplayAlerts = False
ActiveWorkbook.Unprotect Password:="123"
Sheets(Array("CAL1", "CAL2", "CAL3", "CAL4", "CAL5")).Delete
ActiveWorkbook.Protect Password:="123"
Application.DisplayAlerts = False 
End Sub

But unfortunately, same problem happened as I mentioned above.

My research: I have done some searching and found a link here

https://www.mrexcel.com/forum/excel...annot-save-workbook-excel-keeps-crashing.html

I also have 87 named range in this workbook. But I'm unable to understand the problem link between named range and delete sheets.

Please guide me on this. Thanks in advance.

Thanks & regards,

PritishS
 
Last edited:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
I've never had the problems that you're describing. I know you prefer deleted, but could you live with the sheets being "very hidden"?

Your first attempt code:

replace
Code:
Sheets(X).Delete

with
Code:
Sheets(X).Visible = XlVeryHidden

Note: A "very hidden" sheet can only be unhidden by VBA. Right clicking on the tab will bring up an option to "unhide" sheets, but very hidden sheets are not included in that list.
 

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113
Hi!!

Thanks for your response.

I'm not able to live with sheet veryhidden:) . Because in my program I need to delete this "CAL1", "CAL2", "CAL3", "CAL4", "CAL5" (these are basically template from another excel workbook) 5 sheets every time and import fresh template while starting new project. That's why I want to delete those sheets.

I've never had the problems that you're describing.

I have made a fresh file for testing and done the same. There is no excel crashing!!

I guess there may be something with 'NAMED RANGE' as I shared this link https://www.mrexcel.com/forum/excel...annot-save-workbook-excel-keeps-crashing.html

But unable to understand what solution he gave.
Please let me know if any further idea you got.

Thanks & Regards
PritishS
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,459
Office Version
  1. 365
Platform
  1. Windows
An alternative approach to consider.
Create a new file, and copy the sheets you want to keep to that file, and then save that file.
 

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113

ADVERTISEMENT

An alternative approach to consider.
Create a new file, and copy the sheets you want to keep to that file, and then save that file.

Sir, I'm afraid that is also not a good option maybe. It's 10 MB excel file where I have modified many things over the years.

But I have a good news for me:)....while fighting with this I thought to give a chance to macro recorder. I just started recording macro and selected 5 sheets and right clicked and deleted those sheets. Then I stopped recorder.

I got this

Code:
Sub deleteSheets()


Application.DisplayAlerts = False
ActiveWorkbook.Unprotect Password:="123"
'Sheets(Array("CAL1", "CAL2", "CAL3", "CAL4", "CAL5")).Delete  '''Commented this line which I was trying earlier
'''----Below two lines I got from Macro recorder-------
Sheets(Array("CAL1", "CAL2", "CAL3", "CAL4", "CAL5")).Select
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Protect Password:="15203030"
Application.DisplayAlerts = False
End Sub

Now I do not know how this code is working. My 10MB excel file to saving properly without any crashing. Neither my "Buttons(Shapes)" got deactivate nor any other problem arrived. For me know just temporary it's fine.

But this code is more efficient in this purpose, because here no need to mention sheet name.

Code:
Sub DeleteSheetsBetweenFirstAndLastMarkerSheets()
  Dim X As Long
  Application.DisplayAlerts = False
  For X = Sheets("Last_Sheet").Index - 1 To Sheets("First_Sheet").Index + 1 Step -1
    Sheets(X).Delete
  Next
  Application.DisplayAlerts = True
End Sub

I'll keep searching the solution. Please anyone having any idea reading the same, please let me inform.

Thanks & Regards
PritishS
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
Actually this is a very rare case where the recorded code is probably more efficient because it's deleting all the sheets as an array rather than one at a time with a loop. In any event, you're only deleting 5 sheets, so we're talking about nanoseconds difference :) .
 

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113

ADVERTISEMENT

Hi Everyone!!

As I said I'll keep fighting with this:biggrin:.... I found the solution. The macro recorder saved me this time..

Code:
Sub DelNew()
Dim X As Long
ActiveWorkbook.Unprotect Password:="123"
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  For X = Sheets("Last_Sheet").index - 1 To Sheets("First_Sheet").index + 1 Step -1
    'Sheets(X).Delete  '''----Commented this line and used below 2 Lines to delete sheets
[COLOR=#ff0000]    Sheets(X).Select[/COLOR]
[COLOR=#ff0000]    ActiveWindow.SelectedSheets.Delete[/COLOR]
  Next
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  ActiveWorkbook.Protect Password:="123"
  
End Sub

This code works fine. Though I'm unable to find the real reason behind the problem....may be some combination suddenly created that 'Rare' problem. I suspect direct deletion of worksheets is somehow problem....just a guess!! Will let you know if I find something further.

Thanks everyone for spearing your valuable times.

Thanks & Regards,

PritishS
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Would an approach like this work for you?
Code:
Sub DeleteSheetsBetweenFirstAndLastMarkerSheets()
    Dim WS As Worksheet
    Dim DoDelete As Boolean

    DoDelete = False
    ActiveWorkbook.Unprotect Password:="123"
    For Each WS In ActiveWorkbook.Worksheets
        Select Case WS.Name
        Case "First_Sheet", "Last_Sheet"              'Marker sheets
            If Not DoDelete Then
                DoDelete = True
            Else
                DoDelete = False
            End If
        Case Else
            If DoDelete Then
                Debug.Print "Delete sheet " & WS.Name
                Application.DisplayAlerts = False
                WS.Delete
                Application.DisplayAlerts = True
            End If
        End Select
    Next WS
    ActiveWorkbook.Protect Password:="123"
End Sub
 

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113
Would an approach like this work for you?
Code:
Sub DeleteSheetsBetweenFirstAndLastMarkerSheets()
    Dim WS As Worksheet
    Dim DoDelete As Boolean

    DoDelete = False
    ActiveWorkbook.Unprotect Password:="123"
    For Each WS In ActiveWorkbook.Worksheets
        Select Case WS.Name
        Case "First_Sheet", "Last_Sheet"              'Marker sheets
            If Not DoDelete Then
                DoDelete = True
            Else
                DoDelete = False
            End If
        Case Else
            If DoDelete Then
                Debug.Print "Delete sheet " & WS.Name
                Application.DisplayAlerts = False
                WS.Delete
                Application.DisplayAlerts = True
            End If
        End Select
    Next WS
    ActiveWorkbook.Protect Password:="123"
End Sub
Thanks for your code....let me test it and I'll get back to you!!

PritishS
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,619
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top