Using the With Statement You can use the With statement to eliminate the need for repetitive object references, without setting an explicit object variable. The example in the preceding section could be rewritten as follows, using the With statement. The following example calls the Workbooks method and the Sheets method only once each. Next loop to iterate through a collection or array is faster than using an indexed loop. The following example is slow because it sets the row variable thisRow by calling r.
Next loop keeps track of the row count and position.
Your code can get variable values faster than it can get property values. Therefore, if your code gets the value of a property within a loop, it will run faster if you assign the property to a variable outside the loop and use the variable instead of the property inside the loop. The following example is slow because it gets the Value property each time through the loop. Next The following example is faster than the preceding one because the value of one property has been assigned to the variable cv before the loop begins. If you're using an object accessor inside a loop, try to move it outside the loop.