none
Excel with VBA: How to delete a MS Excel Objects (sheet) RRS feed

  • Question

  • I have been given an Excel Workbook which uses Macros/VBA to generate sheets as MS Excel Objects. There seems to an issue with a particular sheet. So I thought, if I could delete this particular sheet - it might resolve the issue.

    In the VBA editor to delete the sheet154(EGF816). Using Module 1, with the suggested code, as follow:

    Sub Delete_Sheet()

    Application.DisplayAlerts = False

    Worksheets("Sheet154").Delete

    Application.DisplayAlerts = True

    End Sub

    When I do F5 to run it, I still get a  Run-time error  '9': subscript out of range.

    Any advise would be highly appreciated - thanks!

    Saturday, November 11, 2017 3:13 PM

All replies

  • M,
    re:  error 9 when deleting sheet

    The worksheet name doesn't exist.
    You should use the exact name displayed on the worksheet tab (including any spaces).
    Also, you can delete a sheet without using VBA by right-clicking the sheet tab and choosing "Delete"

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2  (Dropbox)
    (free & commercial excel add-ins & workbooks)


    Saturday, November 11, 2017 4:19 PM
  • Hi MSA001,

    You need to check if a target sheet ("Sheet154" in your case) does exist.
    I made a sample: use a function "IsSheetExist" (argument is sheet name you want to delete).
    Sub Delete_Sheet()
        Application.DisplayAlerts = False
        ' ---------------------------
        Dim shtName As String
        shtName = "Sheet154"
        ' ---
        If (IsSheetExist(shtName) = True) Then
            Worksheets(shtName).Delete
            MsgBox "sheet: " & shtName & " deleted !!"
        End If
        ' ------------------------
        Application.DisplayAlerts = True
    End Sub
    ' --- Function: check if a specific sheet exists
    Private Function IsSheetExist(ByVal shtName As String) As Boolean
        Dim sht As Worksheet
        For Each sht In Worksheets
            If (sht.Name = shtName) Then
                IsSheetExist = True
                Exit Function
            End If
        Next
        ' ---
        IsSheetExist = False
        MsgBox "sheet: " & shtName & " not found !!"
    End Function
    

    Regards,

    Ashidacchi

    Saturday, November 11, 2017 11:02 PM