As I understand it, your macro is recording the equivalent of the <Ctrl>+<Down Arrow> shortcut key, which selects all cells below the active cell until an empty cell is found. The problem you're experiencing is that your cells contain formulas, so even if they appear blank they are not treated as blank by this command and are selected anyway.
I'm not aware of an easy way to make Excel ignore blank values when selecting cells, but you could solve this a different way by applying a filter to your data that will hide the empty cells.
Hidden cells are not charted by default, so they will not appear on your chart.
Filters are covered in Expert Skills Lesson 1-3: Apply a simple filter to a range.
I have attached an example worksheet showing how this could apply to your data:
If you want more control over how the hidden cells are handled, you can do so using the chart settings, as covered in the video lesson: Essential Skills Lesson 5-17: Deal with empty data points.
If you need to automate this, you could record a macro that reapplies the filter each time it runs. For more on recording macros, see the video lesson: Expert Skills Lesson 9-11 Record a macro with absolute references.
I hope this presents a solution, but please feel free to reply if we can offer any further assistance.
Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.