Advanced Excel: Macro Examples

Creating Excel spreadsheet macros is a great way to save time and reduce the possibility of human error by automating repetitive processes. Other reasons for learning how to write VBA macros include: standardization, improve efficiency, expand capabilities, and streamline tasks. Macros in Excel are created through two primary methods: the macro recorder or by writing custom code with the macro editor (or even with notepad). Recording is a great way to figure out syntax or a specific, simple task if you are stuck but writing custom code is far superior, especially if you are dealing with multiple Excel files. Here are a few basic, easy lines of code which are helpful to learn when beginning to create macros. Most of these deal with layout or formatting of your spreadsheet. I typically would add these types of commands to the end of my macro after all the data is where I would like it.


To insert a row above an active cell use this code:

ActiveCell.EntireRow.Insert

Conversely, to insert a  row below the active cell:

ActiveCell.Offset(1).EntireRow.Insert

To insert a new, blank column to the left of the active, selected cell, use this:

ActiveCell.EntireColumn.Insert

Or, to insert a column to the right of the active cell, change to this:

ActiveCell.EntireColumn.Offset(0, 1).Insert


To delete a column, specify the column number (A being 1, B = 2, and so on):


ActiveWorkbook.ActiveSheet.Columns(16).Delete


To add a diagonal line through a range of cells with a macro use the following code:


Range(insert desired range , ie A2:B2).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With

To change the background color to white, select a range of cells then type this:


With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


To create a thick border on the right of a cell:


Selection.Borders(xlEdgeRight).Weight = xlThick


Or, make the bottom border thick:


Selection.Borders(xlEdgeBottom).Weight = xlThick


To Autofit a columns width, specify a range:


Range(AA:CC).EntireColumn.Autofit
Read more about creating Excel spreadsheetmacros.