Excel Graphs - Dont Show Blank Cells

Dave KIlby
Dave KIlby used Ask the Experts™
I am creating a report using VBA, every week it runs the report for the previous month and current month, so every week the number of rows increases.

I have a sheet that has a couple of graphs, these I created manually.  I want to only show rows in graph that has data, so I would like it to be Dynamic.

For example first week of the month the data may only have 26 rows, but by the end of the month it could have 40 -  If i choose A2:A40 the graph look strange as it is half empty with data and the other half empty.  Is there a way to not show if the data is empty of to add the data to the graph dynamically so if there is 26 rows it shows 26, but if there is 35 rows it shows 35 ?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Karen FalandaysTraining Specialist

Try this:
Click on the chart
From the Chart Tools/Chart Design tab select Edit Data/Select Data
Click the Hidden and Empty cells button and see if any of those options help with your display


I tried all the different combinations inside this option area and none of them worked
Kesavan JeganarayananIT Consultant

This option not feasible in Bar charts, But for Line graphs, you can use the Connect the data points with line option

Please refer this link:
Change the chart data source to a table (CtrlT) and the chart should be dynamic.
NoahHardware Tester and Debugger

Hi there! :) I'm a bit late but please do look at the following examples and see if it applies to your case.

I actually recommend that as much as possible you try not to prevent creation of blank records in a table. This adds complexity to a data model because the behavior of null might prove to be a problem later on. You should also prevent the creation of incomplete records since those have missing information and can be lead to a misleading analysis when combined with complete records.

Here is an example if you want the file to hide all empty rows by default: ExampeUsingHideAllTheTimeByDefault.xlsm

Here is an example if you want the file to allow users when they want to hide or unhide the empty rows with a single click of a button: ExampleUsingHideAndUnhideButton.xlsm

I still recommend you clean up those empty rows so here is an example if you want to delete only rows that are completely empty: ExampleToDeleteBlankrows.xlsm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial