Solved

Excel VBA- Delete WS based on DV List Value

Posted on 2013-01-24
3
412 Views
Last Modified: 2013-01-24
Hi Experts,  :)

I'm creating a workbook that auto adds worksheets and name them based on values within range A4:A23.  The user would click on the yellow image to have named sheet(s) created.  

Name Range Sample
I need VBA that'll delete worksheets whose name is in the DV list in cell A2.

Sample DV List
Is this possible?  Sample workbook is attached also

Thank you!
SampleDeleteWSBasedCriteria.xlsm
0
Comment
Question by:"Abys" Wallace
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 38815655
See code/attached worksheet.

Try clicking the button next to A2 to run code.

Sub deletesheet()
    
    Dim e As String, iRow As Long, ws As Worksheet, main As Worksheet
    
    Set main = ActiveSheet
    e = ActiveSheet.Range("A2").Value
    
    ActiveSheet.AutoFilterMode = False
    Range("A3:F3").Select
    Selection.AutoFilter
    
    iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
    
    If Application.WorksheetFunction.CountIf(Range("A5:A" & iRow), e) > 0 Then
        
        For Each ws In ActiveWorkbook.Worksheets
            ws.Activate
            If ws.Name = e Then
                Application.DisplayAlerts = False
                Sheets(e).Delete
            End If
        Next ws
        main.Select
        Range("A2").Select
        MsgBox "Deleted " & e, vbInformation
    Else
        MsgBox "Your selection is not found among the list!", vbExclamation
    End If
    
End Sub

Open in new window

SampleDeleteWSBasedCriteria.xlsm
0
 

Author Closing Comment

by:"Abys" Wallace
ID: 38815815
Shanan212:  Thank you!  works perfect!  and I like the msg box that advises which sheet was just deleted.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38815842
You are welcome :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question