Removing Unnecessary Recorded Expressions

Another reason the macro recorder produces inefficient code is that it cannot tell which options you've changed in a dialog box. The recorder therefore explicitly sets all available options when you close the dialog box. For example, selecting cells B2:B14 and then changing the font style to bold using the Format Cells dialog box produces the recorded macro shown in the following example.

Range("B2:B14").Select
With Selection.Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlNone
    .ColorIndex = xlAutomatic
End With

Setting the cell format to bold can be done with a single line of code without selecting the range, as shown in the following example.

Range("B2:B14").FontStyle = "Bold"

Again, if you think about what you're trying to do with the macro and you look through the lists of properties and methods that apply to the Font object, you'll see that you could also write this macro using the Bold property, as shown in the following example.

Range("B2:B14").Font.Bold = True

You can also experiment with the macro recorder by recording the same operation done different ways in the user interface. For example, if you format a range using the Bold button on the Standard toolbar, the macro recorder uses the Bold property.