Why is my Excel macro slow when think-cell is activated?

A common problem that can cause performance issues in VBA macros is the usage of the .Select function. Each time a cell is selected in Excel, every single Excel add-in (including think-cell) is notified about this selection change event, which slows down the macro considerably. Especially macros that are created using the macro recorder are prone to this kind of problem.

• Office blog: "What is the fastest way to scan a large range in Excel?"
• MSDN: Improving Performance in Excel 2007: "Reference Excel objects such as Range objects directly, without selecting or activating them" (in the section, "Faster VBA Macros").

Example: How to avoid the usage of the .Select statement

Let's take a look at the following simple macro AutoFillTable:

Sub AutoFillTable()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim nCount As Integer
    nCount = iRange.Cells.Count
    For i = 1 To nCount
        Selection.Copy
        If iRange.Cells.Item(i).Value = "" Then
            iRange.Cells.Item(i).Range("A1").Select
            ActiveSheet.Paste
        Else
            iRange.Cells.Item(i).Range("A1").Select
        End If
    Next
End Sub

This function opens an input box that asks the user to specify a cell range. The function moves over all cells in the range. If it finds a cell that is not empty, it copies the cell content to the clipboard. The function pastes the clipboard content into every subsequent empty cell.

AutoFillTable uses the clipboard to copy the cell content. Therefore, the function needs to select each cell that is manipulated so that Excel knows which cell to copy from and into which cell it should paste. The recommended solution is shown in the following function AutoFillTable2:

Sub AutoFillTable2()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim iCellMaster As Excel.Range
    For Each iCell In iRange.Cells
        If iCell.Value = "" Then
            If Not iCellMaster Is Nothing Then
                iCellMaster.Copy (iCell)
            End If
        Else
            Set iCellMaster = iCell
        End If
    Next iCell
End Sub

AutoFillTable2 differs in two important ways from its predecessor:

1. Most importantly, it does not need to select a cell anymore. Instead, when a cell is found that is not empty, this cell is stored in the variable iCellMaster. Thereafter, whenever an empty cell is found, all the content of iCellMaster is copied into iCell.
2. It uses the Visual Basic language feature For Each … Next to visit every cell in the range. That is already an improvement to legibility.