CODEWITHSUNDEEP
vbaexcel
KISHOR BHOIR
KISHOR BHOIR

Reputation: 89

Delete multiple sheets at once

I have multiple sheets with data on each sheet. I want to delete sheets from sheet number 4 to unlimited. I have tried below code:

Sub Delete_Sheets()
    Application.ScreenUpdating = False
    Dim j As Integer
    j = Worksheets.Count
    For k = 4 To j
        With Sheets(k).Delete
        End With
    Next k
    Application.ScreenUpdating = True
End Sub

The problem is, every time the code ask my permission to delete sheet and finally saw deletes only few sheets. Someone help.

Upvotes: 2

Views: 14358

Answers (3)

L42
L42

Reputation: 19727

If you delete sheets indexed 4 onward always then maybe this will work for you.

Dim idx, s As String

Application.DisplayAlerts = False '/* of course you need to turn this off */

If Sheets.Count > 3 Then
    s = "=TRANSPOSE(ROW(A4:A" & Sheets.Count & "))"
    idx = Evaluate(s)
    Sheets(idx).Delete
End If

Application.DisplayAlerts = True

This will always delete sheets in excess of 4.
But make sure that your sheets are always correctly indexed, otherwise use loop.

Upvotes: 0

Carol
Carol

Reputation: 471

Turn off displayed alerts. Delete backwards from the last sheet.

Sub Delete_Sheets()
Dim j as long
Dim k As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    j = Worksheets.Count
    For k = j To 4 Step -1
        Sheets(k).Delete
    Next k
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Upvotes: 5

user4039065
user4039065

Reputation:

Turn off confirmations and loop the delete worksheet command until you are left with three worksheets.

Option Explicit

Sub qwea()
    Application.DisplayAlerts = False
    Do While Worksheets.Count > 3
        Worksheets(Worksheets.Count).Delete
    Loop
    Application.DisplayAlerts = True
End Sub

Your code would have worked correctly if you had reversed the increment of the For ... Next like For k = j To 4 step - 1. You were 'stepping over' worksheets while deleting.

Upvotes: 3

Related Questions