Programmatic row insertion error; KB211769 no help

[Aaron Rolloff]

Let me start by saying that I have read the Knowledge Base article #211769,
in addition to dozens of other posts regarding row insertion errors, but have
yet to find a solution that fixes this problem for me.

I am receiving the "To prevent possible loss of data, Microsoft Office Excel
cannot shift nonblank cells off the worksheet." error when I try to
programmatically insert a range via VBA macro:
Worksheet.Range("MyRange").Select
Worksheet.Rows.Insert (xlShiftDown)

Here are some additional details:
• I am working with Windows XP Professional & Excel 2003 (11.8302.8221),
both w/SP3
• I am able to manually perform the desired insert at design time, but
cannot do the same when I call the macro from a compiled DLL or the Visual
Studio 2005 IDE.
• My worksheet contains no comments, objects, or merged cells.
o <F5> [Special...] 'Comments' produces a "no cells were found" message
o <F5> [Special...] 'Objects' produces a "no objects found" message
• <ctrl><end> places the cursor in the last cell used in my worksheet (K3)
o I have deleted all unused rows & columns in the worksheet & saved, to no
avail.
• The named range I am selecting is a single row that spans the entire width
of my used area (A-K3).

My original worksheet was far more complex, but I have gone so far as to
start with a completely new worksheet and the 3 empty rows that I am trying
to populate programmatically, ensuring that there are no merged cells or
other objects n the document, yet I still receive the error.

Can anyone provide additional suggestions/assistance??

Thank you in advance for any help,
Aaron

 

[Bernie Deitrick]

Aaron,

The problem is with the ROWS.Insert - you cannot insert a ROW into the ROWS collection because there
is a set number of ROWS on a sheet. You can insert a row, however, just not within the ROWS
collection of the sheet. Go figure...

Try it this way:

Range("MyRange").Insert

or

Range("MyRange").EntireRow.Insert

And note that -

Worksheet

needs to be

ActiveSheet
or
Worksheets("Name of Sheet")

at least from within VBA - maybe not from within a DLL or COM, but I don't know those
restrictions....

HTH,
Bernie
MS Excel MVP

 

[Aaron Rolloff]

We have a winner! Your solution corrected the problem in both my simplified
worksheet, as well as my original, more complex template. In retrospect,
that method makes much more sense than what i was attempting to do
previously.

Thank you so much for your help!!!
Aaron

Aaron,

The problem is with the ROWS.Insert - you cannot insert a ROW into the ROWS collection because there
is a set number of ROWS on a sheet. You can insert a row, however, just not within the ROWS
collection of the sheet. Go figure...

Try it this way:

Range("MyRange").Insert

or

Range("MyRange").EntireRow.Insert

And note that -

Worksheet

needs to be

ActiveSheet
or
Worksheets("Name of Sheet")

at least from within VBA - maybe not from within a DLL or COM, but I don't know those
restrictions....

HTH,
Bernie
MS Excel MVP

Let me start by saying that I have read the Knowledge Base article #211769,
in addition to dozens of other posts regarding row insertion errors, but have
yet to find a solution that fixes this problem for me.

I am receiving the "To prevent possible loss of data, Microsoft Office Excel
cannot shift nonblank cells off the worksheet." error when I try to
programmatically insert a range via VBA macro:
Worksheet.Range("MyRange").Select
Worksheet.Rows.Insert (xlShiftDown)

Here are some additional details:
. I am working with Windows XP Professional & Excel 2003 (11.8302.8221),
both w/SP3
. I am able to manually perform the desired insert at design time, but
cannot do the same when I call the macro from a compiled DLL or the Visual
Studio 2005 IDE.
. My worksheet contains no comments, objects, or merged cells.
o <F5> [Special...] 'Comments' produces a "no cells were found" message
o <F5> [Special...] 'Objects' produces a "no objects found" message
. <ctrl><end> places the cursor in the last cell used in my worksheet (K3)
o I have deleted all unused rows & columns in the worksheet & saved, to no
avail.
. The named range I am selecting is a single row that spans the entire width
of my used area (A-K3).

My original worksheet was far more complex, but I have gone so far as to
start with a completely new worksheet and the 3 empty rows that I am trying
to populate programmatically, ensuring that there are no merged cells or
other objects n the document, yet I still receive the error.

Can anyone provide additional suggestions/assistance??

Thank you in advance for any help,
Aaron

 

[Bernie Deitrick]

We have a winner!

You forgot the "Ding, ding, ding, ding!" part ;-)

I'm happy to hear that your problem was fixed.

Bernie

PS I have contacted MS to suggest changes to that KB article.