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 notable. Especially macros that are created using the macro recorder are prone to this kind of problem.
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: