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

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

Answer 1

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 [e-mail]

Answer 2

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

Answer 3

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 [e-mail].