If you're not sure what the Excel Visual Basic for Applications language is (or how it can help you) this blog attempts to explain! Learn what VBA is, and how you can record macros and then replay them again and again to automate work in Excel.

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Tidying up your macro

Alexander Pope wrote:

A little learning is a dangerous thing;
drink deep, or taste not the Pierian spring:
there shallow draughts intoxicate the brain,
and drinking largely sobers us again.

Finding your Macro

You can't edit a macro until you can find it. To do this, press ALT
+ F11 to go into the VBA code editor (this opens
up a separate application). You should then find your macro:

Expand the workbook into which you recorded your macro (here
the personal macro workbook), then expand the
Modules category. You can
then double-click on Module1.

A module is the name given to the place in which
VBA programming code is stored. Think of it as a blank sheet of paper into
which Excel writes instructions in its own special language.

Tidying up the code

Here are some ideas for how to simplify your code! Note that your macro
won't look exactly the same as this one, but you should be able to get ideas
from what follows. Don't worry about making changes - you can add, delete
and edit lines in the code window as you see fit, to change the way that your
macro behaves.

Perhaps a good start is to remove unnecessary comments:

Comments are the lines which begin with an apostrophe
- ' - and are in green. The ones shown selected add no value, and can be deleted!

The next bit copies a contestant's name - I've added a comment before it:

'copy the contestant's name

Range("C4").Select

Selection.Copy

The next thing is to go to the first blank cell on the Results
sheet, and paste (again, I've added comments):

'go to the results sheet

Sheets("Results").Select

'select the top of the list of names, and

'from there go down to the bottom (note
that we switched from

'absolute to relative recording mode
while recording this)

Range("B4").Select

Selection.End(xlDown).Select

'go one cell further (to first blank cell)

ActiveCell.Offset(1, 0).Range("A1").Select

'now paste in contents of clipboard

ActiveSheet.Paste

When the macro has finished copying and pasting, it's time to look at the
formatting. Firstly, my macro removes any background colour from the
pasted cells:

'clear the "dancing ants" effect - we've finished copying

Application.CutCopyMode = False

'select the whole row to be formatted

ActiveCell.Offset(0, -3).Range("A1:D1").Select

'remove any background colour

With Selection.Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

EndWith

The code to change the font size of the left-hand cell is a fair bit longer
than it needs to be:

'if you change the font size, Excel records a command

'for every part of the FORMAT CELLS dialog box

'all we need is ...

Selection.Font.Name = "Calibri"

'the original code is left in here for reference

' With Selection.Font

' .Name = "Calibri"

' .Size = 14

' .Strikethrough = False

' .Superscript = False

' .Subscript = False

' .OutlineFont = False

' .Shadow = False

' .Underline = xlUnderlineStyleNone

' .ColorIndex = xlAutomatic

' .TintAndShade = 0

' .ThemeFont = xlThemeFontMinor

' End With

The code to set vertical alignment likewise reproduces every part of the
Alignment tab of the FORMAT CELLS dialog box:

'change the vertical alignment to centred

'(all unnecessary lines commented out)

With Selection

' .HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

' .WrapText = False

' .Orientation = 0

' .AddIndent = False

' .IndentLevel = 0

' .ShrinkToFit = False

' .ReadingOrder = xlContext

' .MergeCells = False

EndWith

The code to set borders carefully considers every possible part of a cell's
borders:

When setting the borders for a cell, you can
set:

Based on
this information, you should be able to simplify these lines of code!

Saving your Changes

Excel will automatically save any changes you've made to your personal macro
workbook when you exit the application (or you could press CTRL
+ S or click on the usual disk icon to save your
changes).

It's now time to run your macro to see what it does! There are
several ways to do this ...