Moving down a chart when inserting rows

[ctor]

I've been inserting some rows in some sheets and everything is sound... Until I meet a chart! Charts in general are terrain I yet have to explore (and will be coming Monday)...

I insert a row like this:

Please Login or Register  to view this content.

Works like a charm, it keeps formatting and the dreaded merged cells where I need to keep them, but! Say it inserts 20 rows, but it encounters a chart, the rows keep getting inserted, but the chart isn't moved down accordingly!

[Richard Buttrey]

Hi,

Rather than shifting cells, (which is what you are doing as opposed to inserting rows), why don't you insert rows instead. That way your chart will move automatically.
e.g.

Please Login or Register  to view this content.

HTH

[ctor]

Inserting an entire row is not an option due to certain styling in other columns.

[Andy Pope]

If you are talking about the chart object shape not moving with insertion then check the Move & Size properties of the chart.

Note the chart will only be affected if it is position within the range that is changing.

[ctor]

The rows I'm shifting are above the chart, after a few rows it comes across it and though new rows get shifted in, the chart remains at its position
I was expecting it to shift down accordingly ;(

[Andy Pope]

If I place a chart in row 10 across C:H columns. I can then insert cells in the range C9:H9 and the chart object moves down.

Can you post example of your non moving chart?

[Richard Buttrey]

Hi,

One way would be as follows.
Name a cell within the range you are moving 'Anchor'
Make a note of the number of rows and columns that the chart is offset.

Now use the following where r & c in the .Offset are the constants for the offset.

Please Login or Register  to view this content.

HTH

[Andy Pope]

Richard, not sure I completely follow but rather than copy/paste/delete the chart when not just more it?

Please Login or Register  to view this content.

Also the paste will cause the name of the chart to change every time.

[Richard Buttrey]

Hi Andy,

I thought about that but wouldn't you have to work out where the top of the chart should be located with reference to the sum of the pixel count for all the rows down to the new location? i.e. how would you calculate the .Top?

That seemed to be more effort but I'd welcome your comment.


Regards

[Andy Pope]

You would indeed need to know where the chart was to be positioned. Just as you would when cutting and pasting.

The .Top changes the position of the chart.

[Richard Buttrey]

Ahh! I've just twigged. I see what you mean.

Incidentally, when copying and pasting a chart it seems that the copied version of the chart has the same name as the original. How can that be? When the named chart is deleted it deletes the original version, or it did when I tried it and leaves the copy.

I'm slightly puzzled by this.

Rgds

[Andy Pope]

which version of xl gave the copied chart the same name.

I tested in xl2003, which did not.

[Richard Buttrey]

Andy,

xl2007. I first noticed this a couple of months ago with another workbook and didn't think too much about it at the time.

Regards

[Andy Pope]

Do you have SP2 for office installed?
My xl2007 version increments the chart index when done manually or with code.

[ctor]

Sorry for the late response guys.
I made a small impression of my situation.

The real life situation is a bit more complicated though, there are several chart objects on a few different worksheets in this workbook.
What I'm trying to accomplish is a generic as possible solution.

Inserting an entire new row certainly works, but that's not the correct solution for this case.
I'm looking for a method to insert/shift rows into the outlined region, and by doing so the chart should move down. Inserting an entire row is (in the real life situation) no satisfactory solution.

[Andy Pope]

This works for me.

Please Login or Register  to view this content.

If you only want the cells that are actually bordered, A4:J4, then you will need to adjust the position/size of the chart object so it is all contained within those columns, as currently it over hangs column K

[ctor]

If you only want the cells that are actually bordered, A4:J4, then you will need to adjust the position/size of the chart object so it is all contained within those columns, as currently it over hangs column K

Yeah, but I'm not able to do that on every sheet, due to the layout this template is blessed with.
The project has some... nasties
I've worked it out using the IncrementTop property of the Shape class.
I just added an optional parameter to my shift method, if it's set to true then it iterates over the shapes in de Shapes collection and moves the shape 12.75 * the number of rows that need to be inserted/shifted in.
Damn this code is in need of some refactoring. AGAIN!

[Richard Buttrey]

Hi Andy,

Yes I have SP2 installed.

You are right in that if you accept the default chart names, i.e. Chart1, Chart2 etc. each time you copy and paste a chart then the copy is incremented by 1.

However when you name a chart, then copy and paste it, the copy seems to have the same name. When I select and delete the original the copy remains with the original name.

Somehow it doesn't seem right that two charts, (or two anythings for that matter) can have the same name. Fortunately I've not encountered any problems with this but it just seems odd.

Regards

[Andy Pope]

When chart is named via code and copied I can reproduce.

That is a nasty problem just waiting to catch someone out!

[Richard Buttrey]

Thanks Andy,

It's nice to have your confirmation and know it's not just me!

Rgds