title: Excel VBA - replace non-english characters
body: <p>im currently mapping some addresses from one excel sheet into another, this requires me to do specific things such as changing the column orders, deleting duplicate rows, and cutting the page into 100-row sheets...i also need to replace non-standard characters with their closest English equivalent.</p>
  
  <p>I have a bit of code to accomplish this, and it works fine but its really slow, it looks like this:</p>
  
  <pre><code>Sub ReplaceCharacters()
  'Replaces special characters for English
  Dim rCell As Range
      For Each rCell In ActiveSheet.Range("A1:H10000").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
          With rCell
              .Value = Application.WorksheetFunction.Substitute(.Value, "Á", "A")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Å", "A")
              .Value = Application.WorksheetFunction.Substitute(.Value, "á", "a")
              .Value = Application.WorksheetFunction.Substitute(.Value, "å", "a")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ð", "D")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ð", "D")
              .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
              .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
              .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ó", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ó", "o")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ý", "Y")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ý", "y")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Þ", "Th")
              .Value = Application.WorksheetFunction.Substitute(.Value, "þ", "th")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Æ", "AE")
              .Value = Application.WorksheetFunction.Substitute(.Value, "æ", "ae")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ø", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ø", "o")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ä", "A")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ä", "a")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "U")
              .Value = Application.WorksheetFunction.Substitute(.Value, "À", "A")
              .Value = Application.WorksheetFunction.Substitute(.Value, "à", "a")
              .Value = Application.WorksheetFunction.Substitute(.Value, "È", "E")
              .Value = Application.WorksheetFunction.Substitute(.Value, "è", "e")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ì", "I")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ì", "i")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ò", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ò", "o")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ù", "U")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ù", "u")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ç", "c")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ç", "C")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Â", "A")
              .Value = Application.WorksheetFunction.Substitute(.Value, "â", "a")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ê", "E")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ê", "e")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Î", "I")
              .Value = Application.WorksheetFunction.Substitute(.Value, "î", "i")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ô", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ô", "o")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Û", "U")
              .Value = Application.WorksheetFunction.Substitute(.Value, "û", "u")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ñ", "N")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ñ", "n")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ã", "A")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ã", "a")
              .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
              .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ë", "E")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ë", "e")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
              .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ï", "I")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ï", "i")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ö", "o")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
              .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ú", "U")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "U")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ü", "u")
              .Value = Application.WorksheetFunction.Substitute(.Value, "Ÿ", "Y")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ÿ", "y")
              .Value = Application.WorksheetFunction.Substitute(.Value, "ß", "ss")
              .Value = Application.WorksheetFunction.Substitute(.Value, "œ", "oe")
          End With
      Next rCell
  End Sub
  </code></pre>
  
  <p>I was wondering if there is a quicker way to accomplish the same end result of swapping non-standard characters with their english equivalents?</p>
  
  <p>Also, i cant seem to get my head around how to ask the code to just sample all active rows, hence im needing to overestimate the number of rows:
  ActiveSheet.Range("A1:H10000").SpecialCells(xlCellTypeConstants, xlTextValues).Cells</p>
  
  <p>Any ideas how to just get it to change all active rows on the sheet?</p>
  
  <p>Many thanks, im all very new to this :D</p>