"Can't shift excess data off page"???

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

This code should find a certain cell, drop down one row, and insert a new
blank row:
wb1.Activate
Columns("C:C").Select
Selection.Find(What:="D000368", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, 0).Activate
Selection.EntireRow.Insert

The debugger didn't want to insert the row, telling me it couldn't shift
data off the page. I have plenty of room below my last row, so I'm assuming
that when I took it from the recorder to here and put in my parameters, I
coded something wrong. (Just a wild guess, you understand ... 8>} ).
Any help is appreciated.

Ed
 
Ed,

First try changing your code to
Columns("C:C").Find.....
I don't think you need the select part.

Secondly you may have done something to make Excel think you are using every
row.

Do a Ctrl + End and see what row it takes you to. This row should be
somewhere near the end of your data, not down at 65536

If it is down to 65536 you need to delete a bunch of rows to fix your
worksheet. The easiest way to select ANY cell just below your data, hold
down the Shift key and do the Ctrl + End. Than delete all the rows
selected. (may take a while if it is down to 65536) Than Save.

You may also need to understand how this happened.
It could have happened by inadvertantly putting in a character entry
(sometimes a single space), some kind of formating, or ??? Another way is
if there is a defined name that specifies this row.

This is a common problem with Excel and drives a lot of people buggy until
they find out what happened (not without lots of lost sleep and lots less
hair).

Hope this helps!

steve
 
Steve:

Ctrl+End takes me to Row 6700, the end of my data. The file is the return
of a database query (*very* old database program) which is converted out to
an Excel v3 file. I am opening this file in Excel2000.

Just for grins and giggles, I tried this on a previous query return which
had been modified and saved as xl2k. Same error, same place - "Excel cannot
shift nonblank cells off the worksheet."

I'm open to more suggestions.

Ed
 
Back
Top