Last week I discussed how to remove Select and Selection statements as a way to clean up recorded macro code. In the example I presented, this method reduced the number of lines of statements by over 50 percent and also cut the running time in half. While cutting the running time in half might not be a big deal when you go from 5 seconds to 2.5 seconds, as your macros get bigger and more ambitious, improving performance takes on greater importance.
Would you believe that by adding two lines of code to last week’s example I was able to cut the running time in half again? It’s true — two simple lines of code. Rather than show you the same code again, I have a new example that demonstrates this simple performance tip.
Have you ever needed to hide rows of data in Excel that meet certain conditions? Listing 1 shows a simple procedure that hides each row where the value in column 1 is 100 and the value in column 2 is 200. To keep things really simple here, I’ve just fixed the number of rows examined at 100 rows.
Listing 1: A demonstration of Application.ScreenUpdating
Sub HideRowsFast()
' Reset the worksheet
UnhideAllRows
' Call the HideRows procedure
' so that it uses Application.ScreenUpdating
HideRows True
End Sub
Sub HideRowsSlow()
' Reset the worksheet
UnhideAllRows
' Call the HideRows procedure
' so that is does not use Application.ScreenUpdating
HideRows False
End Sub
Sub HideRows(bGoFast As Boolean)
Dim nRow As Integer
Dim ws As Worksheet
Dim dTimer As Double
dTimer = Timer
Set ws = ThisWorkbook.Worksheets("Sheet1")
If bGoFast Then Application.ScreenUpdating = False
For nRow = 1 To 100
If ws.Cells(nRow, 1).Value = 100 And _
ws.Cells(nRow, 2).Value = 200 Then
ws.Cells(nRow, 1).EntireRow.Hidden = True
End If
Next
If bGoFast Then Application.ScreenUpdating = True
Set ws = Nothing
MsgBox "That took " & Timer - dTimer & " seconds.", vbOKOnly
End Sub
Sub UnhideAllRows()
ActiveSheet.Cells.EntireRow.Hidden = False
End Sub
The magical lines of code that can dramatically improve the performance and appearance of your macros are the ones that use Application.ScreenUpdating. For the purposes of this demonstration, I put them in an If/Then statement so that you could easily run it with or without using ScreenUpdating. In practice, all you need to do is surround any block of code that would affect the user interface with Application.ScreenUpdating = False and then set it back to Application.ScreenUpdating = True when you are done.
The effect on performance depends on how often you do something in code that would cause Excel to redraw the display. To experiment, put 100 in the first column and the first 100 rows of Sheet1. Likewise, put 200 in the second column and the first 100 rows. Then, run HideRowsSlow and HideRowsFast, noting the elapsed time of each. On my computer, HideRowsSlow takes about 1.03 seconds. HideRowsFast meanwhile only takes 0.03 seconds!
If you delete everything below row 20 however, HideRowsSlow only takes about 0.17 seconds while HideRowsFast only takes about 0.02 seconds (actually 0.0151).
The moral of the story is always precede a block of statements that will modify the contents or formatting of a worksheet with Application.ScreenUpdating = False. Just be sure to set Application.ScreenUpdating = True when you’re done. You get a substantial performance gain and your work will look more professional as the screen won’t flicker as your macro goes about its business.