CODEWITHSUNDEEP
vbaexcel
joshjayse
joshjayse

Reputation: 77

VBA Excel: Deleting all charts and graphs in a workbook, except one

I have a macro that generates a lot of worksheets and charts. There's also various subroutines that run so the names and quantity of each worksheet/chart generated is never the same. What is constant is my HOME worksheet which is the UI for the user and I want it to be unaffected.

I found a similar question here about deleting all worksheets except the one you are working with (i.e. HOME). Here's what I have so far.

Sub ZRESET()

Dim ws As Worksheet, wb As Workbook
Set wb = ActiveWorkbook

    Sheets("HOME").Select

    Application.DisplayAlerts = False
        For Each ws In wb.Worksheets
            If ws.Name <> "HOME" Then
                ws.Delete
            End If
            If Chart.Name = "" Then
                Charts.Delete
            End If
        Next
    Application.DisplayAlerts = True

    Range("B5:E5,B9:E9,B13:E13,B14:E14").ClearContents
    Range("A1").Select

End Sub

The worksheets delete fine, the hang up I have is the charts. I tried various attempts to remove charts and sometimes they work (i.e placing Charts.Delete outside of a FOR loop and IF statement). But this requires me to actually have a chart in the workbook. Sometime the user can just develop worksheets but no charts.

Any advice to continue my goal of deleting SHEETS and/or CHARTS, while keeping my HOME sheet intact?

Upvotes: 0

Views: 12296

Answers (2)

perfo
perfo

Reputation: 412

Option Explicit

Sub GetRid()
  Dim ASheet As Worksheet
  Dim AChart As Chart


  Application.DisplayAlerts = False
  Application.ScreenUpdating = False

  '** first scan for and delete all non HOME worksheets ***
  For Each ASheet In ActiveWorkbook.Worksheets
    If UCase(ASheet.Name) <> "HOME" Then
      ASheet.Delete
    End If
  Next

  '** Now scan and delete any ChartSheets ****
  For Each AChart In ActiveWorkbook.Charts
    AChart.Delete
  Next

  Application.DisplayAlerts = True
  Application.ScreenUpdating = True

End Sub

Upvotes: 1

perfo
perfo

Reputation: 412

Option Explicit

Sub AllSheetsAndcharts()

  Dim AChart As ChartObject
  Dim ASheet As Worksheet
  Application.DisplayAlerts = False
  For Each ASheet In ActiveWorkbook.Worksheets
     If UCase(ASheet.Name) <> "HOME" Then

       For Each AChart In ASheet.ChartObjects
         AChart.Delete
       Next
       ASheet.Delete
     End If
  Next
  Application.DisplayAlerts = False

End Sub

Upvotes: 0

Related Questions