title: Remove a defined name or broken reference in excel using C#
body: <p>My aim is to delete all the <code>"#REF"</code> from within an excel file in the workbook.xml. What I can currently do is find all the "#REF" references throughout my workbook however I'm not sure how to delete them from within the file. </p>
  
  <p>My code is as fallows</p>
  
  <p>This piece of code finds all the <code>"#REF"</code> in the file</p>
  
  <pre><code>        var spreadsheet = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(@"C:\Users\Craig\Desktop\newTest.xlsx", false);
          var workbook = spreadsheet.WorkbookPart;
          var names = workbook.Workbook.DefinedNames;
  
  
  
  
  
          System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"#REF");
  
          int count= result.InactiveNamedRangeCount = names.Count(n =&gt; regex.IsMatch(n.InnerText));
          Console.WriteLine(count);
          foreach (var name in names)
          {
              if (regex.IsMatch(name.InnerText))
              {
                  Console.WriteLine(name.InnerText);
              }
          }
  </code></pre>
  
  <p>And here I'm trying to go though each <code>#REF</code> to delete it using a foreach but it doesn't delete any of them and I'm not sure why.</p>
  
  <pre><code>foreach (var name in names)
          {
              if (regex.IsMatch(name.InnerText))
              {
                  name.Remove();
                  Console.WriteLine(name.InnerText);
                  Console.WriteLine(count);
              }
          }
  </code></pre>