IF/macro paste special/empty values into chart problem

Hello,

I am attempting to make a workbook with a graph to show weekly sales and total percentage of stock sold.

My problem is that with my current set up, when plotting the line for 'sell through' the chart will not recognise the empty cells when I select 'connect values with line'.

In column A I have week 1, week 2 etc. In column B I have sales in £ for the week (this column is where the data will be input but will sometimes have a zero value). This is then linked to the value of the stock amount on sheet 1 to produce a cumulative percentage of the value of stock sold in column C. I have then used an IF formula to copy this column removing any duplicate data into column D, and recorded a macro linked to 'button b' (which is to be used to updates the chart after new data is entered) to 'paste special' values (without formulas) into column E which the chart then pulls its data from. Columns C and D will be hidden and locked.

I think the problem is that the macro leaves the cell completely empty (rather than being 'blank' or 'zero' which the 'connect values with line' setting for the chart will recognise) as if i click in the cell and press delete the point on the graph will be removed.

Once this is figure out I intend to replicate sheet 2 multiple times for all of the stores (1-40) on sheet 1.

Any help will be greatly appreciated! I'm using excel 2011 for mac if that matters.

I am a total novice at excel so if you need further information please let me know and I will provide.

Thank you!