Unable to insert columns

[pqt]

Happy New Year to All Members,

I have an issue that need some help. I am not able to insert a column in Excel file 2013, but it is ok to insert a new row. Attached is the error message:

Capture.JPG

The last cell having data is S1610. The last end point setting cell is Y1635.

I also attached screenshot of the sheet so you can see last data endpoint in red set at Y1635.



I think if anyone that help to move end point cell to another cell location inside the sheet like V1635. That should help.

I don't know how to do it .

Thanks

[MarvinP]

Hi pqt,

Try to delete a few of the farthest columns on the right of your sheet. It looks like you have some data way over to the right. Maybe you put a number over there by mistake?

[pqt]

HI,
Thanks for reply.
This sheet was set dada end point cell by newdoverman at cell Y1635. Now I want to re-set it to new cell V1635. This should help. I don't know how to do it.
Note that last cell having entered data is S1610. The last end point cell that was previously set is Y1635.
There is no column on the right of column Y.
However, There are plenty of rows below row 1635.
That is why it is ok to insert rows, but unable to add column.
Thanks
PQT

[ELeGault]

You have hidden cells to the right? (Columns) and if not - do you use alot of "Comments".

Excel typically causes this sort of headache due to shifting cells to where there is not enough room to display the comments in a default space to the right. So I am going out on a limb and assuming you hide columns and use comments.

If there are not a lot of comments then I would recommend clicking on the cells that are present and right click>Show/Hide comments so that they are visible. You can now move them to the left side of the cell and enter your columns as needed. Personally I avoid comments in data as you cannot report on them in any easy manner and they are not readily visible to anyone assisting in use of such data.

I suspect that is the source of your issue...and if I am wrong well ignore all that was just said

Cheers, and Happy New Years!

[newdoverman]

The method that I used was to use the Excess Format Cleaner available from Microsoft for free download. It is an add-in for Excel. It is available at http://xsformatcleaner.codeplex.com/ which was a link from a Microsoft site. Install and run it.

I have found that it does a better job than the outlined manual method of deleting rows and columns. Others may have different "mileage".

[MarvinP]

Hey pqt,

It seems that the Add-In made this workbook smaller by resetting the last "possible" column.

I'd copy your data and paste it into a new workbook. Then see if you can insert a new column with the new workbook.

[newdoverman]

It also appears that you have hidden columns from column Y to the end. If you have data in column Y, your last column, you will not be able to insert a new column. Select column Y AND at the same time move the cursor right as if there were columns there and then select UNHIDE.

[ELeGault]

So now that I can see your image from a different PC - You do have hidden columns already, and you do have comments - I have no doubt that if you expose your comments they are living on the edge of the visible fields. Hiding and or inserting will shift them into the abyss which Excel will not allow by default. Post the file and I can test this out or even point to the exact comment that is causing this issue... at the end of the day I am sure you need to do as I previously stated and unhide your comments and move them back to where you originally had them... I suspect further that you have inserted many columns since those initial comments were added which is why they live so far to the right and not right on top of the cell they originated from.

[newdoverman]

I wonder why you are hiding the columns at all. I can't see any gain from doing so. If you don't want to see cell borders why not format several columns (out of visual range) with white borders. These hidden columns on the right can cause more trouble than they are worth.

[pqt]

Hi,
I finally got it work. Thank you all for help. I found several row having data and format, especially borders for all cells in 2 rows. Those are ones that prevent the sheet from inserting new columns.
pqt