Error: Cannot Shift Items off Sheet

[AlexanderBB]

This has just started to appear as the workbook loads.
It continues to load and everything seems fine .. until I try to Hide or Unhide something.
Some cols are ok and some bring up the error again.

Is there anything I can do to fix it ?

 

[Fazza]

hello, Please reset the used range - so delete unused rows & columns. OK?

 

[AlexanderBB]

There's no unused Cols on the right, it stops at "Z". (I must have done this somehow)
There was formatting on unused rows below the last filled row, so I cleared them.
Bur it hasn't helped, problem remains. AFAIK I have no "used range".

 

[Michael M]

Is this caused by a workbook_open macro ??
It usually means sonething like you are copying a whole row and trying to paste it in col "B" or greater thereby trying to paste off the sheet

 

[AlexanderBB]

The whole worksheet (Cols A-Z) were populated by a macro (reading in from an ADODB connection).
Row 1 was filled in Excel first, though - with the column Names.
Should I have set something to the whole sheet first? I'm not following what you mean by "paste off the sheet".

 

[Fazza]

If the WHOLE worksheet is filled, then you can't do much about it. You've a lot of data

 

[Fazza]

Or maybe (via Google) this is your situation?

https://support.office.com/en-ie/ar...in-excel-559f37da-2b7f-4548-a58d-96669f5310d6

 

[Michael M]

What I'm saying is:
If you were to copy and ENTIRE row...then try to paste it anywhere in the same / other sheet, but in a different column, Excel would give that error, as an ENTIRE row MUST be pasted in col "A"

 

[AlexanderBB]

Thanks for replies. I think I've found the source, each row has a Comment in one of the columns (not the last one though).
I've removed them all and the problem goes away. Trouble is, I want them.
What are the limits for Comments? Maybe it was some of them not all, causing the problem? Can I find which if that's the case?

 

[simon31]

<img src="https://www.pixelsbin.com/images/2019/06/13/sheet2-not-updated67fa228c1756eee6.png" alt="sheet2-not-updated67fa228c1756eee6.png" border="0" />