Runtime error (1004) when trying to hide rows containing chart data

Friends...

I have a sheet which contains input cells, formulae and two charts. One section deals with the result of a physical test (ie plug in the results), with a second section taking an empirical approach. Which is used depends on a variable set in another sheet. I have a 'hidelines' routine which loops down the rows in the sheet and hide/shows each row based on a value in a specified column (23) which is itself set based one the variable in the other sheet. In each section the chart is after the rows containing the chart data.

When the sheet is activated a routine is run to hide the lines as explained above. The first 25 lines go fine but then just before it gets to the first chart it bombs with Error 1004. In fact it doesn't actually reach the row with the chart. It appears that it can't quite hide the data ranges referred to the chart and it's this that causes the error. These data ranges are approx 5 rows deep but after the error they are represented by a thin horizontal line at the level of the last hidden row.

I've tried hiding the chart first but this has no effect. It appears the process of hiding rows just can't cope if the rows contain data which is linked to a chart.

Does anyone have any words of wisdom?

On a slightly tangential subject I also appear to have issues hiding any ActiveX controls such as option buttons and control boxes which again, don't seem to appreciate being 'hidden' by the row they are on.

Thank you

Hi jazzer, apologies for the delay, but I was travelling...

Ok when I try and manually hide the rows, which includes the data series and the chart, I get an error stating "Cannot shift objects off of sheet".

Interestingly I got this same error before when trying to hide columns which included the text boxes of cell comments.

Hiding just the rows with the data values works fine but it complains about trying to hide the chart.

Ok, with sheet protection off, if I set the chart format properties to :

Chart locked, then either

'Move and size with cells' - hiding the rows works and the chart is hidden too, unhide brings it all back
'Move but don't size with cells' - hiding the rows around the chart works, but leaves the chart starting at the top of the now hidden rows
'Don't move or size with cells' - as the one above, hides rows but leaves chart, unhide puts chart back correctly

With all of the above, trying to hide both data rows and chart together results in the same error message as above.

Repeating the tests above with chart Locked off and/or Print Object on/off makes no difference

I also tried hiding the data and chart ranges in two goes data first, then chart or chart rows first, then data rows. in both cases the first hide went fine but I got the same error when trying to hide the second. Excel clearly doesn't like hiding both at the same time!

Any thoughts ?

ps it's difficult to post the sheet as it's one of approx 100 in a WB. However if necessary I'll have a go though not sure how to upload here....

Thanks again. R