none
VB 2012 & Excel "Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet. These cells might appear empty but have blank values, some formatting, or a formula. Delete enough rows or columns to make room for RRS feed

  • Question

  • The business has an Excel 2016 XLSX file that is updated monthly. A small amount of data from this file needs to be uploaded to our mfg system. I have a Visual Basic 2012 application that opens Excel and striped off the excess data for easier upload. Once all the data is striped out of the file, the data is reformatted and additional columns are added. The application has been working for years. There have been no changes to my system. The business user recently made changes to the file but only to delete some columns/rows. Now the application continues to get the below error message.


    "Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet. These cells might appear empty but have blank values, some formatting, or a formula. Delete enough rows or columns to make room for what you want to insert and then try again."


    I have attempted all the suggestion that I found on-line, including deleting/clearing all the columns to the right/bottom of the data. Since there are formulas within the data, I can not copy and paste special values. Additionally when I step through within debug to code that is failing, I can manually insert the columns within Excel without an error. It only seems to fail when using the command below within my VB application.


    objSheet.Columns(1).Insert(Shift:=(Microsoft.Office.Interop.Excel.Constants.xlRight))


    Any help would be appreciated

    Thursday, January 11, 2018 6:25 PM

All replies

  • Hi Joy,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about Excel development. Since our forum is to discuss the VS IDE, I will help you move this thread to the appropriate forum for a better support, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, January 12, 2018 5:13 AM
  • I believe this was moved to the wrong forum.  This is the Microsoft Access for Developers forum, not an Excel forum.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, January 12, 2018 7:20 PM
  • Hi Joy1218,

    You just posted the one line of code.

    I try to made a test with that one line.

    Sub Main()
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
    
            xlApp = New Excel.Application
            xlApp.Visible = True
            xlWorkBook = xlApp.Workbooks.Open("C:\Users\v-padee\Desktop\Copy of demoxl.xlsx")
            xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
            xlWorkSheet.Columns(1).Insert(Shift:=(Microsoft.Office.Interop.Excel.Constants.xlRight))
    
    
        End Sub

    Output:

    I find that line is working fine on my side.

    You can see that one line of code is not enough to reproduce the issue.

    The issue is not related with code, it is related with your file. you need to check your Excel file and check how the changes affects the execution of code.

    Further, i suggest you to post the sample code with your sample workbook.

    Which can able to reproduce the issue on our side.

    We will try to make a test with it and try to find the solution for it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, January 15, 2018 6:46 AM
    Moderator