Excel : can't delete a row "you can not change part of the array"

[pho01]

When I was trying to delete a row, an windows pops up: "you can not change part of the array", then it stucks on a cell, can't get out of that cell to do anything else. What is that message, How do i delete that row?

Thanks!

 

[GlennUK]

That message means that one or more cells in that row belongs to an array of formulae, spanning an area that includes the row that you want to delete.

Look at the formulae in that row, and look for a formula that is surrounded by { } ( curly brackets ), as this indicates arrat formulae. Select the cell containing the array formula, and press Ctrl-/ , this will highlight the area covered by the array. You will need to delete the array before being able to delete the row.

Cheers, Glenn.

 

[pho01]

Thanks Glenn
When I press ctrl-/, it highlighted the area. But when I try to delete something form that highlighted area, the windows pops up again saying 'you can not change part of the array'.

how do I delete the array?

 

[GlennUK]

You can't delete something from the hightlighted area, you have to delete the entire highlighted area.

If that destroys some formulae that you are going to need, then you need to recreate them after doing the deletion.

Cheers, Glenn.

 

[KenWright]

It is likely a single formula spanning several cells in a column. Select the entire array as suggested and NOTE THE RANGE IT COVERS, then hit F2 and then CTRL+ENTER. This will break the array, so don't worry if you see lots of errors appear. Now delete your row.

When you have done that, you will now need to manually select the range that the array had previously covered (possibly one row less though), then hit F2 and then CTRL+SHIT+ENTER. This should reinstate your array.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------