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.
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:
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:
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
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
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