I hate doing tedious work. Why should I spend minutes or even hours repeating the same action over and over again? If I can find a way to get the computer to do the work, I will. In programs like Microsoft Excel, Word, and PowerPoint, a macro is that way. Technically speaking, a macro is a series of actions that can be automated, so you can execute them whenever you desire.
For example, I often need to paste information from a database into Excel. Unfortunately, after its pasted, the data is not formatted the way I want. And as shown below, it is ugly and hard to read.
To fix the data I need to do the following:
First, select and bold the headings in row 1. You can use the mouse to select row 1, then press Ctrl B for bold.
Second, select all the data by pressing Ctrl A.
Third, turn off text wrapping. From the ribbon’s Home tab click Wrap Text twice. You need to click it twice because initially some cells have wrap text on and other have it off. The first click therefore turns wrap text on for every selected cell, and the second click turns wrap text off.
Fourth, autofit the width of each column. Assuming the data columns are still selected, from the ribbon’s Home tab choose Format, AutoFit Column Width.
Fifth, if any column is too wide, resize it with the mouse or select the column and use the Home, Format, Column Width… feature. You may also want to turn on wrap text (Home, Wrap Text) for that column.
Sixth, reselect all the data again (press Ctrl A), and then change the vertical alignment to top.
Finally press Ctrl Home to return the cursor to cell A1, and then freeze the top row so the column headings stay in place when you scroll down (View, Freeze Panes, Freeze Top Row).
As shown below, the results are much better.
Unfortunately, the results took seven tedious steps. It would be much better if you could click one button and have the computer execute the seven steps for you. That is the beauty of a macro. You save time, and you know the computer will do the work the right way, every time.
Below is the macro code that will execute those seven steps.
‘Create a variable to represent a column.
Dim col As Range
‘Step 1: Bold the headings in row 1.
Rows(“1:1”).Font.Bold = True
‘Step 2: Select all the cells.
‘Step 3: Turn off text wrapping.
Cells.WrapText = False
‘Step 4: Autofit the width of each column.
‘Step 5: Resize any column with a width greater than 50.
For Each col In ActiveCell.CurrentRegion.Columns
If col.ColumnWidth > 50 Then
‘Set column width to 50.
col.ColumnWidth = 50
‘Wrap the text.
col.WrapText = True
‘Step 6: Set vertical alignment to the top.
Cells.VerticalAlignment = xlTop
‘Step 7: Return to cell A1 and Freeze the top row.
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
If you know how to create and edit a macro you may want to paste this code into your Personal Macro workbook. If you do not yet know how to create a macro, I have two pieces of good news:
First, the next few TechMentor blog posts will show you how to plan, create, write, edit, and test macros.
Second, if you want to learn right away, you can sign up for TechMentors Excel VBA Macro class.