Results 1 to 2 of 2

Thread: Worksheets Delete Macro From Master List

  1. #1

    Worksheets Delete Macro From Master List



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am trying to delete some worksheets within a workbook from a list containing the names of worksheets to be deleted, listed in column B2:B30, in sheet titled " Sheetlist". I obtained the following code from a thread, the code seems to work if column B in the specified range are all populated. If it is empty, as in the example, the code does no work.

    In "SheetList" sheet, cell A2 onwards in column A contain all the names within the workbook. Cell B2 onwards in column B to cell B30 will be the area which I specify the sheets I need to delete.


    Can you please help to modify the code so that the macro will loop through each cell from B2:B30 eventhough it is empty, and delete worksheets name which are listed?

    Thanks.

    Mark

    Sub DeleteSheets()
    Dim i As Long
    For i = 2 To 30

    Sheets("SheetList").Select
    Application.DisplayAlerts = False
    Worksheets(CStr(ActiveSheet.Cells(i, 2).Value)).Delete
    Application.DisplayAlerts = True
    Next i
    End Sub

    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    719
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hello Mark

    I'd use this function that I found somewhere on the net a long time ago

    Code:
    Private Function SheetExists(sname) As Boolean
        ' Returns TRUE if sheet exists in the active workbook
        Dim x As Object
        On Error Resume Next
        Set x = ActiveWorkbook.Sheets(sname)
        If Err = 0 Then SheetExists = True _
            Else SheetExists = False
    End Function
    then use a macro like this

    Code:
    Sub DeleteSheetsInColB()
        Dim rng As Range    'the range containing sheet names to be deleted
        Dim cel As Range    'each individual cell within that range of sheet names
        
    'set the range of sheet names to be deleted
    Set rng = Sheets("Sheetlist").Range("B2:B30")
    
    'prevent Excel's warning about deleting sheets
    Application.DisplayAlerts = False
    
    'step through the list of sheet names
    For Each cel In rng
    'check that the cell is not empty
        If Len(cel.Value) > 0 Then
            'cell contains name of a sheet, check if sheet actually exists
            If SheetExists(cel.Value) = True Then
                'delete the sheet
                Sheets(cel.Value).Delete
            End If
        End If
    Next cel
        
    'turn warnings back on
    Application.DisplayAlerts = True
    
    End Sub

    Copy both of these codes into a new module and give a try.

    Good luck with your project. Hope this is of some assistance.
    Last edited by NoS; 2013-11-24 at 06:22 PM.

Posting Permissions

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