entering columns

New Contributor

When I try to add a column in an existing worksheet, I get a message:

"can't insert new cells because it would push non-empty cells off the end of the worksheet------"

 

when I press end>home, I am taken to last column of the spreadsheet (column XFD) and 2865 rows down to cell XFD2865.  I donot not see anything in this cell. I have tried deleting columns, removing all formats, but nothing solves the problem
I use office 365 and my worksheet uses only up to cell BD10000

Any suggestions what to do?

 
 
 
2 Replies

@Evert_Bolt Just to be sure that you have gone through all the options, allow me to ask a few questions. Check Workbook statistics from the Review ribbon. Does it tell you that BD1000 is indeed the last cell in your sheet?

After deleting all columns towards the left of BD, did you save the file, close it and re-open it?

 

Or perhaps you have accidentally merged all cells of an entire (empty) row. Then, you will not be able insert any columns either.

Hi @Riny_van_Eekelen 

I checked all parameters again and also size of the workbook indicates the last cell in the workbook is last available row on the right. (84MB)

I again checked for any formats or accidental entries but to no avail.
I Have 2 macro's ( buttons) in the sheet - to protect and unprotect the sheet ( to protect against accidental data changes)

Seems unlikely however that this caused the unused columns to fill up

 

I have now copied all the relevant cells to a new worksheet and re-created the macro's and linked pivot tables again.
Now all is normal again and work book size is down to less than 1 MB

 

I must say that this work book I created 7 years ago , so maybe it was time to re-wite it.