Delete all sheets in between two sheets

[PritishS]

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:

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"

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

 

[jproffer]

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

Sheets(X).Delete

with

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]

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]

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]

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

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.

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]

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]

Hi Everyone!!

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

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]

Would an approach like this work for you?

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]

Would an approach like this work for you?

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