File error: data may have been lost and Excel replaces Ranged Names insite spreadsheet

Question

Hi guys,

I have an issue i am not able to solve. I built about 75k namedRanged and not the Excel shows the error : " File Error : data may have been lost" and when i open the file some rangedNames are replaced within the formula and formulas are lost. I tried to delete all ranged names and file opens. When i recreate all cell names i get the error at the end. I use round(A1+A2/ Divisor, round) in many cells. I also tried to check if Named Manager opens and close it when no issues, but unable to close Named Manager via VBA. Please help.

Answer 1

Thanks for reply, converting to another extension and repairing the spreadsheet using the method you mentioned did not solve the issue. Is there anyway i can check using VBA or something what causing the issue? Thanks

Answer 2

Hi

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Sorry for the trouble this might have caused. You can try to repair the file using the built-in Excel Repair tool. Go to File > Open > Browse, select the file, and click on the arrow next to the Open button. Select Open and Repair from the dropdown menu.

It's also possible that the formula you are using is causing the issue. You may want to try using a simpler formula to see if that resolves the issue.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 3

Thank you. I read that officially there is no defined name limit in Excel, but you are saying that there is. When i try to add names to more cells the file stops working. Do you know what is official defined names limit? Thanks

Answer 4

Hi Daemonnyc,

It sounds like the issue you're experiencing could be due to the large number of named ranges in your Excel file. Excel has a limit on the number of named ranges that can be used in a single file, and exceeding this limit can cause unexpected behavior, such as the error message you're seeing.

One solution would be to try to reduce the number of named ranges in your file. This can be done by consolidating similar named ranges, deleting any unused named ranges, and using range references instead of named ranges wherever possible.

Otherwise, try these manual workarounds-

1. Try to recover an earlier version of the file: If you have saved multiple versions of the file, you may be able to recover an earlier version that is not corrupt. To do this, go to File > Info > Manage Workbook > Recover Unsaved Workbooks. This will open a folder with any unsaved files that may contain an earlier version of your file.
2. Use the Open and Repair feature: Excel has a built-in feature called "Open and Repair" that can help repair a corrupt file. To use this feature, open Excel, go to File > Open, select the file, click the drop-down arrow next to the Open button, and then click Open and Repair.
3. Try to repair your excel using a Excel recovery tool: There are several third-party recovery tools available that can recover data from corrupt Excel files. Some popular options include EaseUS Data Recovery, Stellar Phoenix Excel Repair, and Recovery for Excel.

I googled your query and hopefully, this blog can help you out as well.

Answer 5

Use VBA to loop through all the named ranges in your workbook and display their formulas. You can use the following code:

Sub ListNamedRanges() Dim n As Name For Each n In ThisWorkbook.Names Debug.Print n.Name & " = " & n.RefersTo Next n End Sub

This will display the name and formula of each named range in the Immediate Window. You can then review the formulas and identify any errors.