Excel Named ranges corrupted when a worksheet is deleted

Question

Excel 2010 VSTO 2010 Visual Sudio 2010 .NET 4.0

I have a workbook with six worksheets. Nothing very complicated; some simple formulas in some of the cells.

On start up, I use a dialog box to ask which version the user wants. Based on the response, I have to delete one worksheet or another worksheet.

There are named ranges on both worksheets that are used to sum a column of numbers. I can make those named ranges specific to the worksheet or I can make them global to the workbook. It does not make any difference.

When I delete a worksheet, some, but not all, of those named ranges get corrupted. If I do not delete a worksheet, everything works fine.

Any ideas?

---

jim...

Answer 1

Hello Jim,

Thanks for posting. How these named ranges are defined? by code or manually? From what you said, it seems that there is a code snippet about how to use named ranges. Please ensure that there is no code for the named ranges which was on deleted worksheet.

For this issue, would you please simplify a project/steps about this issue and share it with us? So I could use it on my side to reproduce this issue for further research.

We are looking forward to your reply. Have a nice day.

---

Bessie Zhao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Thank you for your speedy reply.

The named ranges are mostly defined through the Name Manager on the Ribbon.

They are used once on each of two worksheets in the formula =SUM([named range])

I have made them associated with the sheet and associated with the workbook. It seems to make no difference.

The named range for the worksheet that was not deleted gets corrupted exactly after the deletion (in code) occurs.

I developed a work-around last night by hiding the worksheet worksheet instead of deleting it. If I hide the worksheet, no corruption of the named ranges occurs.

The project starts with a template that contains six worksheets. There are two sheets for two different versions of the final workbook. They are mutually exclusive. I pop up a dialog box in the this.Workbook startup code to ask the user which version they want. Based on their response, I delete one worksheet or the other.

The VSTO code adjusts itself based on which choice was made. 

---

jim...

Answer 3

Hello again Jim,

Glad to know that you have found a workaround for this issue by hiding the worksheet. Also thanks very much for sharing it with us here.

Actually, I am not able to see this issue on my side. If you are still looking for some information for this issue, would you mind tell us more information here?

What do you mean making them be associated with sheet/workbook?  

There are two sheets which have different versions and are mutually exclusive. Right? How these two sheets are in one worksheet? Do I miss any point? Would you mind simplifying a project/steps about this issue and sharing it with us?

Have a nice day.

---

Bessie Zhao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Excellent questions.

1. The two worksheet are mutually exclusive but both have a column of prices for which I need a total.  Both worksheets are in the same workbook. So both named ranges name a group of cells in one column. They are summed later in that column with =SUM([named range]) Note: the summed column is different in the two worksheets.

2. Using the Name Manager under Formulas, in the middle of the window is a drop down that lets you associate a Named Range with a Workbook or a Worksheet.

3. To further confuse the issue, there are two formulas that were converted to Excel 2010 from Excel 2003. They are not affected by the problem of corruption when a worksheet is deleted.

4. Important: it is not just the formulas for the worksheet that gets deleted that get corrupted, it is the formula for the worksheet that remains that gets corrupted.

---

jim...

Answer 5

Mark this as not fixed but with a viable workaround. Hiding the sheet instead of deleting it does not corrupt the named ranges and either way, the users don't see the worksheet.

---

jim...

Answer 6

Excel 2010 VSTO 2010 Visual Sudio 2010 .NET 4.0

I have a workbook with six worksheets. Nothing very complicated; some simple formulas in some of the cells.

On start up, I use a dialog box to ask which version the user wants. Based on the response, I have to delete one worksheet or another worksheet.

There are named ranges on both worksheets that are used to sum a column of numbers. I can make those named ranges specific to the worksheet or I can make them global to the workbook. It does not make any difference.

When I delete a worksheet, some, but not all, of those named ranges get corrupted. If I do not delete a worksheet, everything works fine.

Any ideas?

---

jim...

excel recovery works with corrupted excel files after viruses, system errors, unstable software. It restores *.xls, *,xlt, *.xlsx, xlsm, *.xltm, *.xltx, *.xlam file formats. Program starts under Win 98 and higher.