Most of the time, your code can operate on objects without activating them. If you learned Visual Basic programming by using the macro recorder, you're probably accustomed to activating or selecting an object before you do anything to that object. The macro recorder does this because it must follow your keystrokes as you select and activate sheets and cells. However, you can usually write much simpler and faster Visual Basic code that produces the same results without activating or selecting each object before working with it. For example, filling cells C1:C20 on Sheet5 with random numbers (using the AutoFill method) produces the macro recorder output shown in the following example.
Sheets("Sheet5").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("C1:C20"), Type:=xlFillDefault
Range("C1:C20").Select
All of the Select method calls are unnecessary. You can use the With statement to write code that operates directly on the worksheet and range, as shown in the following example.
With Sheets("Sheet5")
.Range("C1").FormulaR1C1 = "=RAND()"
.Range("C1").AutoFill Destination:=.Range("C1:C20"), _
Type:=xlFillDefault
End With
Keep in mind that the macro recorder records exactly what you do — it cannot optimize anything on its own. The recorded macro uses the AutoFill method because that's how the user entered the random numbers. This isn't the most efficient way to fill a range with random numbers. You can do the same thing with a single line, as shown in the following example.
Sheets("Sheet5").Range("C1:C20").Formula = "=RAND()"
When you optimize recorded code, think about what you're trying to do with the macro. Some of the operations you can perform in the user interface (such as dragging a formula from a single cell into a range) are recorded as methods (such as AutoFill) that can be eliminated in the optimized code because there is a faster way to perform the same operation in Visual Basic.