Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 9th, 2007, 02:32 PM
Authorized User
Join Date: Dec 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help on Hiding Charts with no data

Good day all,

I have once again found myself in a bind. The problem statement is pretty straight foward....I have a worksheet full of 182 charts and all of these charts are referenced to cell ranges normally full of data but sometimes, depending on the day and other factors, some of the ranges have no data in them.

The thing is, the boses dont want to have to skip over the charts and graphs that have no data in them just to get to the ones they want to see.

So, I was wondering if there was even a way to do this? I am not all that big in the VBA but have some expierance. This might sound simple to some of you guys, but it really is driving me crazy.

Anyone has any ideas I would be very greatfull!!

Old January 10th, 2007, 05:25 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts

The trouble with the problem as posted is that it's hard to guess exactly what you mean by empty charts and exactly how you'd like to hide up the empty charts.

I've written a bit of code below that will search through the Values of each and every Series in each embedded chart in a worksheet. It deems a chart to be empty if all the values are zero. Please note that this will still allow non-zero x-axis values (which can be found in the XValues variant array of the Series object) which my code ignores. I've also implicitly assumed that you are not using dynamic range names for your input data. Were this to be the case, empty charts would be designated as charts with no values due to your zero-sized ranges and I guess this might crash the code I've written but I'm not too sure with out testing it.

The second point is that I'm not sure what you want doing with the empty charts when found. The code I've written will just hide / unhide charts although clearly on a sheet with 182 charts you're going to be left with a few gaps if you just hide / unhide. Depending on how acceptable this is to you and how motivated you feel, you could write a macro to copy all the non-blank charts to an output chart sheet. The coding of this would be fairly tedious though as you'd have to work out the XY co-ordinate positioning of each new chart.


Sub HideEmptyCharts()

Dim wksCharts As Worksheet
Dim objCO As ChartObject

    ' Set up a variable for the worksheet containing the charts
    Set wksCharts = ThisWorkbook.Sheets("Sheet1")

    ' Loop through every embedded chart object on the worksheet
    For Each objCO In wksCharts.ChartObjects

        ' Make each one visible
        objCO.Visible = True

        ' If the chart is empty make it not visible
        If IsChartEmpty(objCO.Chart) Then objCO.Visible = False

    Next objCO

End Sub

Private Function IsChartEmpty(chtAnalyse As Chart) As Boolean

Dim i As Integer
Dim j As Integer
Dim objSeries As Series

    ' Loop through all series of data within the chart
    For i = 1 To chtAnalyse.SeriesCollection.Count

        Set objSeries = chtAnalyse.SeriesCollection(i)

        ' Loop through each value of the series
        For j = 1 To UBound(objSeries.Values)

            ' If we have a non-zero value then the chart is not deemed to be empty
            If objSeries.Values(j) <> 0 Then
                ' Set return value and quit function
                IsChartEmpty = False
                Exit Function
            End If

        Next j

    Next i

    IsChartEmpty = True

End Function
Old January 10th, 2007, 04:08 PM
Authorized User
Join Date: Dec 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts

Good day,

You are right. I did not make it all that clear. Sorry. All I want is for the code to either always be in effect or execute only when a button is clicked. What I want the code to do is to look for all charts in that worksheet that have no data in them at all (By this I mean that the ranges the chart refer to are empty..no data) and to hide them (By this I mean make not visible). Don't need them to be errased. Infact if they were erased it would not be a good thing. The 182 charts are not dynamic. They always refer to the exact same cells/ranges in the exact same place.

ok... leaving the caps will actually be ok.

I see the code you wrote...would you mind showing me were to paste in the VBA window, and if possible explain to me when this will execute? will it always be running or can it be set to run like a macro withen the worksheet through, Tools/Macro/Macros/Run....The later would actualy be better in my case so I could just a a button on the worksheet to press like a "refresh".

Thanks again friend!

Old January 10th, 2007, 04:22 PM
Authorized User
Join Date: Dec 2006
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts


Thank you Friend....It worked just as I had planned!

WOW..that was great. Once again I have been saved from almost sure dimise by this page.

You guys ROCK!

I have a question for you, what do you guys get in return for helping smucks like me out?...lol! I am learning more and more everytime. One of these days I will be able to help others out to.

Really thanks Maccas!

Old December 19th, 2018, 03:04 AM
Registered User
Join Date: Dec 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

I need help I have 12 charts and I want to hide the charts with no data in it. can you help?

Similar Threads
Thread Thread Starter Forum Replies Last Post
reg hiding panel containing data acc to role veda Visual Studio 2005 1 July 8th, 2008 07:52 AM
charts MunishBhatia ASP.NET 2.0 Professional 0 May 28th, 2007 11:52 AM
about charts MunishBhatia ASP.NET 2.0 Professional 1 May 11th, 2007 05:40 AM
Hiding the last Separator in a data bound DataList .Netster ASP.NET 2.0 Basics 1 March 8th, 2007 11:14 AM
Variable data ranges for charts Cclhodges Excel VBA 1 June 12th, 2003 02:18 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.