title: Alternate to Replace function cell by cell - VBA
body: <p>I currently have a few dozen columns with hundreds of rows for each. One of the subs that I am calling goes and modifies the data of the cells after it comes in from an XML hosted on the web. The current method works, but it tends to get a bit slow since it does the changes cell by cell. Here is my code:</p>
  
  <pre><code>Private Sub fixEnt(listCol As ListColumn) 'fixes the HTML/XML entities
      Dim rng As Range
      On Error Resume Next
      Set rng = listCol.DataBodyRange.SpecialCells(xlCellTypeConstants)
      On Error GoTo 0
      If Not rng Is Nothing Then
          For Each areaItem In rng.Areas 'iterate over the arrays in the Areas array
              For Each cell In areaItem 'iterate over the values in the Item array
                  cell.Value = decodeEnt(cell.Value)
              Next
          Next
      End If
  End Sub
  </code></pre>
  
  <p>which calls on decodeEnt:</p>
  
  <pre><code>Private Function decodeEnt(cellVal As String) As String
  
      Dim tempStr$ 'holds new value after replace
      tempStr = Replace(cellVal, "&amp;#34;", Chr(34)) 'Chr(34) is a "
      tempStr = Replace(tempStr, "&amp;#39;", "'")
      tempStr = Replace(tempStr, "&amp;#38;", "&amp;")
      tempStr = Replace(tempStr, "&amp;#60;", "&lt;")
      tempStr = Replace(tempStr, "&amp;#62;", "&gt;")
      tempStr = Replace(tempStr, "&amp;#160;", " ")
      tempStr = Replace(tempStr, "&amp;#35;", "#")
      tempStr = Replace(tempStr, "&amp;nbsp;", " ")
      tempStr = Replace(tempStr, "&amp;lt;", "&lt;")
      tempStr = Replace(tempStr, "&amp;gt;", "&gt;")
      tempStr = Replace(tempStr, "&amp;quot;", Chr(34))
      tempStr = Replace(tempStr, "&amp;apos;", "'")
      tempStr = Replace(tempStr, "&amp;amp;", "&amp;")
      tempStr = Replace(tempStr, "&amp;ndash;", "–")
      tempStr = Replace(tempStr, "&amp;#252;", "ü")
      tempStr = Replace(tempStr, "&amp;deg;", "°")
      tempStr = Replace(tempStr, "&amp;auml;", "ä")
      tempStr = Replace(tempStr, "&amp;uuml;", "ü")
      tempStr = Replace(tempStr, "&amp;rsquo;", "’")
  
      decodeEnt = tempStr 'Return modified string
  End Function
  </code></pre>
  
  <p>Is there a quicker way of performing that operation? Something that modifies the data all at once in the rng.Areas array perhaps? Speed is key for this project, but I'm out of ideas in this case.</p>
  
  <p>Thanks</p>
  
  <p><strong>EDIT:</strong> To further clarify about the project. It imports an XML file from an API of another tool and saves it into a table in Excel. I have other code that refreshes the connection, appends all data from the XML (new and old). Once the refresh process is complete it begins the data modification which include fixing the HTML/XML entities in the cells and fixing the format for dates. After the modifications are done, , it removes duplicate rows (since there is no way of adding only new data when doing a refresh).</p>
  
  <p>Hope this clears up any confusion.</p>