Systematically Renaming Ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to NOT show zero values on a chart and despite some good information
via this site (ie Tools>Options>Chart the "plot empty cells" GUI is
disabled), I think I may need another method via VBA, so....

I have a range named "ChartData" and it refers to the populated cells a1:b7.

What I need to do is, if cells a5:b7 are blank, systematically redefine the
"ChartData" range to then refer to a1:b4. (via sorting the range, I can
ensure all the blank data is at the bottom of the ranges).

If this can be accomplished, then the chart data range will only have data
and my issue with NOT showing zero values will be resolved.

If anyone can either provide the code to accomplish this or suggest a more
efficient alternative, please help.

Any and All Help Will Be Appreciated - Thanks In Advance
 
instead of sorting and renaming you could filter the data.
faik hidden lines (filtered out) will not appear on the chart.
 
sort the data and use defined names for your data source

Insert =>Name=>Define

Name:= listA
Refersto = =Offset(Sheet1!$A$1,0,0,Count(Sheet1!$B$1:$B$7),1)

Add Button

Name:= listB
Refersto = =Offset(Sheet1!$B$1,0,0,Count(Sheet1!$B$1:$B$7),1)

Add Button

Use the names ListA and ListB as your data sources.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using VBA to swap between a clustered or separated column chart 4
VBA set thick borders around non empty cells in range 0
VBA to update Chart Source Data Rage on Multiple Charts 1
Select variable range 1
Excel JUMP FROM SEARCH BAR TO RESULT 3
JUMP FROM "SEARCH" CELL TO RESULT WHEN PRESSING ENTER 1
Nearly There? 8
Setting chart data range automatically 4

Back
Top