Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatic row insertion error; KB211769 no help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatic row insertion error; KB211769 no help
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatic row insertion error; KB211769 no help
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 "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatic row insertion error; KB211769 no help
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
programmatic conversion of XLSX into XLS | Excel Programming | |||
Programmatic Hiding | Excel Discussion (Misc queries) | |||
Programmatic export to CSV | Excel Programming | |||
programmatic advanced filter | Excel Programming | |||
Programmatic updates | Excel Programming |