Error: Cannot Shift Items off Sheet

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,591
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
hello, Please reset the used range - so delete unused rows & columns. OK?
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,591
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,957
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jul 1, 2009
Messages
1,591
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
If the WHOLE worksheet is filled, then you can't do much about it. You've a lot of data
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,957
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jul 1, 2009
Messages
1,591
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

Board Regular
Joined
May 31, 2019
Messages
61
<img src="https://www.pixelsbin.com/images/2019/06/13/sheet2-not-updated67fa228c1756eee6.png" alt="sheet2-not-updated67fa228c1756eee6.png" border="0" />
 
Last edited:

Forum statistics

Threads
1,077,731
Messages
5,335,893
Members
399,056
Latest member
CityGirlLuv

Some videos you may like

This Week's Hot Topics

Top