Run-time error 1004: Cannot shift nonblank cells off the worksheet
 

I have a macro running that creates a row, runs calculations, makes
references (values, not links), then deletes the row.

It works fine for several runs, but then I get this 1004 error.

"Run-time error '1004':

To prevent posssible loss of data, Microsoft Office Excel cannot shift
nonblank cells off the worksheet.

Try to locate the last nonblank cell by pressing CTRL+END, and delete
or clear all in cells between the last cell and the end of your data.
Then select cell A1 and save your workbook to reset the last cell
used."



If I save the file and exit out, it works fine for a few more runs. Its
a macro that will only be ran 1-2x per week, but still I want to make
sure this wont pose an issue several months down the road when someone
is performing data entry. I won't post the entire code, because it was
recorded, and incredibly long..but if you feel that would help I can
post it up.

FWIW, this is the actual piece of code giving the error:
Selection.Insert Shift:=xlToRight

Run-time error 1004: Cannot shift nonblank cells off the worksheet
 

If you are really working with rows, I would think you want to use
xlShiftDown not xlShiftToRight.

And, when you get the message, where does Ctrl+End put you? Where your data
ends or off in never-never land?
If you end up off in column IV or row 65536 then you need to DELETE (not
Clear) the extraneous Rows & Columns, save & close your work and check
Ctrl+End again. Repeat until Ctrl+End puts you where you would expect to be.
*Then* test your macro. Then check Ctrl+End. If you end up where you expect
then you'll probably be ok from then on.

HTH,
--
George Nicholson

Remove 'Junk' from return address.



wrote in message
oups.com...
I have a macro running that creates a row, runs calculations, makes
references (values, not links), then deletes the row.

It works fine for several runs, but then I get this 1004 error.

"Run-time error '1004':

To prevent posssible loss of data, Microsoft Office Excel cannot shift
nonblank cells off the worksheet.

Try to locate the last nonblank cell by pressing CTRL+END, and delete
or clear all in cells between the last cell and the end of your data.
Then select cell A1 and save your workbook to reset the last cell
used."



If I save the file and exit out, it works fine for a few more runs. Its
a macro that will only be ran 1-2x per week, but still I want to make
sure this wont pose an issue several months down the road when someone
is performing data entry. I won't post the entire code, because it was
recorded, and incredibly long..but if you feel that would help I can
post it up.

FWIW, this is the actual piece of code giving the error:
Selection.Insert Shift:=xlToRight