Improving range browsing performance

Sometimes it’s necessary to browse through all of the cells in a sheet and using ranges can kill your code performance.

The best way to do it is to copy your worksheet to a variant and then browse the variant, the difference in performance is in the order of 50x faster.

Here’s a simplified example on how to do such copy and browsing:

Dim vaOrigVal As Variant, vaDestVal As Variant
Dim strOrigVal As String, strDestVal As String

Dim strRange As String

vaOrigVal = wSheetOrig.Range("A1:F500").Value
vaDestVal = wSheetDest.Range("A1:F500").Value

For lRow = 1 To lRowMax
  For lCol = 1 To lColMax

    'Your code here
    strDestVal = CStr(vaDestVal(lRow, lCol))
    strOrigVal = CStr(vaOrigVal(lRow, lCol))

  Next lCol
Next lRow


Keep coding!