Tutorial on Excel Macros (VBA)      

Excel VBA Consulting

VBA Excel help

Excel functions and formulas


Macros in Excel


Excel Macros Help


VBA for Excel Solutions



Programming in Excel


Macro Vocabulary in Excel


Forms (UserForms) in Excel


Moving around the Worksheet in VBA for Excel

Other methods (Delete, ClearContents, Paste and PasteSpecial) plus errors

Delete or ClearContents

BEWARE: If you write Range("a2").Delete the cell A2 is destroyed and cell A3 becomes cell A2 and all the formulas that refer to cell A2 are scrapped. If you use Range("a2").ClearContents only the value of cell A2 is removed. In VBA Delete is a big word use it with moderation and only when you really mean Delete.


In this piece of code you are copying cells A1:A5 to B1:B5. Notice that you don't have to select cells B1 to B5 to paste just selecting B1 will do. Every time you copy/paste don't forget to empty the clipboard.
Notice and remember that when you paste you paste in ActiveSheet not ActiveCell. On the contrary for PasteSpecial you paste on Activecell, Selection or Range("So andSo")

Instead copying from one cell, moving to another cell and then pasting you can "call" the content of a cell or a range into the Activecell:
Range("V1:V5").Copy Destination:=ActiveCell
Sheets("MySheet").Range("V1:V4").Copy Destination:=ActiveCell

See Copy/PasteSpecial in the "Precautions and Ideas" section


The "Pasting" code above copies the contents of the cells (value or formula) AND THE FORMAT. If you want to paste only the value or only the formula you will use:
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


Don't write the paste special code use the macro recorder and copy/paste the result within your code. You can change manually the xlPasteFormulas for xlPasteValues. Personally every time I start a new project I use the macro recorder at the beginning to create this line of code and I then use it all around my project.

In the code above you will notice the second argument "Operation:=xlNone" this argument can take 5 different values including "Operation:=xlAdd" and "Operation:=xlMultiply". Try this in Excel:

Enter numbers in cells A1 to A5. Enter a value in cell B1 (3 for example) then select cell B1, copy, select cells A1 to A5 and then go to Edit/Paste/Special/Add". You notice that 3 has been added to each cells A1 to A5.

You can use this approach to add, subtract, divide and multiply. It is sometimes faster than using a formula that you copy/paste. It is particularly interesting when you import data from an old legacy system where the numbers are text and with which you cannot execute any calculation. You enter 1 in an empty cell and you Copy/PasteSpecial/Multiply and the numbers become real numbers or the date become dates.

Using the Macro Recorder

Use the Macro Recorder for everything pertaining to format and colors of cells

With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

Selection.NumberFormat= "@"

Error Messages

You will often see the following error message when you code for Cells, Range, Columns and Rows:

It is because you have forgotten a parenthesis or a quotation mark.

You will also see the following error message when you code for Cells, Ranges, Columns and Rows:

when you forget the period.

Discover more tips
and practical examples of real solutions to real problems
with step by step development instructions in the 25

Resource Spreadsheets


Excel VBA Consulting

Excel macros help

Exxcel functions and formulas