© Merry L. Morris

Simplified Excel Macros

Example 1 Font bold, center text in cell Example 2 (Insert and name 12 worksheets)
Example 3 (Create a table)


The macros generated by Excel's macro recorder often contain too many unnecessary lines. After I use the Excel macro recorder to generate a macro, I always study the generated code, then eliminate unnecessary lines.

Excel's macro recorder generated the macro example below to set the font weight to bold and to center align the text in the selected cell.

Sub boldcenter()
        Selection.Font.Bold = True
        With Selection.Font
                .Name = "Arial"
               .Size = 12
               .Strikethrough = False
               .Superscript = False
               .Subscript = False
               .OutlineFont = False
               .Shadow = False
               .Underline = xlUnderlineStyleNone
               .ColorIndex = xlAutomatic
       End With
       With Selection
               .HorizontalAlignment = xlCenter
               .VerticalAlignment = xlBottom
               .WrapText = False
               .Orientation = 0
               .AddIndent = False
               .IndentLevel = 0
               .ShrinkToFit = False
               .ReadingOrder = xlContext
               .MergeCells = False
        End With
End Sub

ALL I wanted was a macro to make the font bold and center the text! So I deleted all the unnecessary lines to create the shorter, simpler version below.

Sub boldcenterevised()
        Selection.Font.Bold = True
        With Selection
                .HorizontalAlignment = xlCenter
        End With
End Sub


To see more examples of generated macros that I have revised to include only the lines of code I need and nothing more, scroll down ...

Example 2           Back to top of page


The two macros shown below both insert 12 worksheets and name the worksheets in Fiscal Year order.

But the version on the left is not concise enough for me!

GENERATED VERSION:

Sub CALENDARSHEETS()
' Macro1 Macro
' Macro recorded by Merry Morris

        Sheets.Add
       Sheets("Sheet4").Select
       Sheets("Sheet4").Name = "JUNE"
       Range("B13").Select
       Sheets.Add
       Sheets("Sheet5").Select
       Sheets("Sheet5").Name = "MAY"
       Range("B7").Select
       Sheets.Add
       Sheets("Sheet6").Select
       Sheets("Sheet6").Name = "APRIL"
       Range("B4").Select
       Sheets.Add
       Sheets("Sheet7").Select
       Sheets("Sheet7").Name = "MARCH"
       Range("C3").Select
       Sheets.Add
       Sheets("Sheet8").Select
       Sheets("Sheet8").Name = "FEBRUARY"
       Range("B28").Select
       Sheets.Add
       Sheets("Sheet9").Select
       Sheets("Sheet9").Name = "JANUARY"
       Range("B1").Select
       Sheets.Add
       Sheets("Sheet10").Select
       Sheets("Sheet10").Name = "DECEMBER"
       Range("C1").Select
       Sheets.Add
       Sheets("Sheet11").Select
       Sheets("Sheet11").Name = "NOVEMBER"
       Range("C1").Select
       Sheets.Add
       Sheets("Sheet12").Select
       Sheets("Sheet12").Name = "OCTOBER"
       Range("B1").Select
       Sheets.Add
       Sheets("Sheet13").Select
       Sheets("Sheet13").Name = "SEPTEMBER"
       Range("B1").Select
       Sheets.Add
       Sheets("Sheet14").Select
       Sheets("Sheet14").Name = "AUGUST"
       Range("C1").Select
       Sheets.Add
       Sheets("Sheet15").Select
       Sheets("Sheet15").Name = "JULY"
       Range("B1").Select
End Sub
whew! 		MY SIMPLE VERSION:

Sub CALENDARSHEETSrevised()

' CALENDARSHEETS Macro
' Macro revised by Merry Morris

       Sheets.Add.Name = "JUNE"
       Sheets.Add.Name = "MAY"
       Sheets.Add.Name = "APRIL"
       Sheets.Add.Name = "MARCH"
       Sheets.Add.Name = "FEBRUARY"
       Sheets.Add.Name = "JANUARY"
       Sheets.Add.Name = "DECEMBER"
       Sheets.Add.Name = "NOVEMBER"
       Sheets.Add.Name = "OCTOBER"
       Sheets.Add.Name = "SEPTEMBER"
       Sheets.Add.Name = "AUGUST"
       Sheets.Add.Name = "JULY"
        End Sub




Example 3           Back to top of page


The two macros below both create the table shown here:


But the version on the left is needlessly long. So I deleted all of the 74 unnecessary lines to create the simplified version on the right.

GENERATED VERSION (145 lines):

Sub CreateTable()
' CreateTable Macro
' Macro recorded by Merry Morris

Range("A1:G1").Select
Selection.Font.Bold = True
With Selection.Font
        .Name = "Arial"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
End With
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Table Title Goes Here"
Range("A2").Select
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
ActiveCell.FormulaR1C1 = "CALL #"
Range("B2").Select
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
ActiveCell.FormulaR1C1 = "AUTHOR"
Range("C2").Select
ActiveCell.FormulaR1C1 = "TITLE"
Range("C2").Select
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
Range("D2").Select
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
ActiveCell.FormulaR1C1 = "PUBLISHER"
Range("E2").Select
Columns("D:D").EntireColumn.AutoFit
Range("E2").Select
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
ActiveCell.FormulaR1C1 = "ISBN#"
Range("F2").Select
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
ActiveCell.FormulaR1C1 = "OCLC#"
Range("G2").Select
With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With
ActiveCell.FormulaR1C1 = "DATE"
Range("A1:G20").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
End With
Range("G25").Select
End Sub

whew! 		MY SIMPLIFIED VERSION: (71 lines)

Sub CreateTableRevised()

' CreateTableRevised Macro
' Macro revised by Merry Morris

Range("A1:G1").Select
Selection.Font.Bold = True
With Selection.Font
        .Name = "Arial"
        .Size = 16
End With
With Selection
        .HorizontalAlignment = xlCenter
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Table Title Goes Here"
Range("A2").Select
With Selection
        .HorizontalAlignment = xlCenter
End With
ActiveCell.FormulaR1C1 = "CALL #"
Range("B2").Select
With Selection
        .HorizontalAlignment = xlCenter
End With
Range("B2").Select
ActiveCell.FormulaR1C1 = "AUTHOR"
Range("C2").Select
ActiveCell.FormulaR1C1 = "TITLE"
Range("C2").Select
With Selection
        .HorizontalAlignment = xlCenter
End With
Range("D2").Select
With Selection
        .HorizontalAlignment = xlCenter
End With
ActiveCell.FormulaR1C1 = "PUBLISHER"
Columns("D:D").EntireColumn.AutoFit
Range("E2").Select
With Selection
        .HorizontalAlignment = xlCenter
End With
ActiveCell.FormulaR1C1 = "ISBN#"
Range("F2").Select
With Selection
        .HorizontalAlignment = xlCenter
End With
ActiveCell.FormulaR1C1 = "OCLC#"
Range("G2").Select
With Selection
        .HorizontalAlignment = xlCenter
End With
ActiveCell.FormulaR1C1 = "DATE"
Range("A1:G20").Select
With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlMedium
End With
With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlMedium
End With
End Sub

Back to top of page