Solved

Excel VBA- Delete WS based on DV List Value

Posted on 2013-01-24
3
408 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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