Program by Levels
Tutorial on VBA for Excel (Macros)
bringing you the simplest and most efficient ways
to do things in VBA for Excel
VBA Lesson 2-6: VBA for Excel for the Cells, Rows and Columns
(Levels: Intermediate and Advanced)
In alphabetical order here are the 30 VBA words that you need to learn to work efficiently within worksheets:
ActiveCell, ArrayFormula, Borders, Cells, ClearContents, ColorIndex, Column, Columns, Copy, Count, CurrentRegion, Delete, End, EntireRow, EntireColumn, False, Font, Formula, FormulaR1C1, Hidden, Insert, Interior, NumberFormat, Offset, PasteSpecial, Range, Rows, Row, Select, Selection, True, Value
We will build simple and complex VBA sentences with all of them in this lesson.
This lesson is divided in four sections:
- Level 1: All the objects, properties and methods to work with cells, rows and columns
- Assisted: All the code that you will never write because the Macro Recorder will do it for you
- Precautions and Ideas: To help you understand certain critical situations and choices
- Level 2: For advanced users.
Lesson 2-6: Level 1
Range
To select a single cell you will write:
Range("A1").Select
To select a set of contiguous cells you will write:
Range("A1:G5").Select
To select a set of non contiguous cells you will write:
Range("A1,A5,B4:B8").Select
A special use of Range
To select a set of non contiguous columns you will not write:
Columns("A:A,C:C,E:E").Select
but:
Range("A:A,C:C,E:E").Select
To select a set of non contiguous rows you will not write:
Rows("1:1,3:3,5:5").Select
but:
Range("1:1,3:3,5:5").Select
Offset
The Offset property is the one that you will use the most with Range. It is the very important property that allows you to move right, left, up and down and to exte the size of the selection.
To move one cell down (to B3): Range("B2").Offset(1,0).Select
To move one cell to the right (to C2): Range("B2").Offset(0,1).Select
To move one cell up (to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (to A2): Range("B2").Offset(0,-1).Select
As you notice the first argument between the parentheses is the number of rows and the second one is the number of columns. Knowing this you will expect an error message if you write:
Range("A1").Offset(-1,0).Select
Because there is no cell to the left of A1
You will also get an error with:
Range("B2").Offset(-2,0).Select
Because there are not 2 cells to the right of B2
If you'd try to execute any of the two lines of code above you would see this:
Here is a piece of code that you will use very frequently if you want to select one cell and 3 more down:
Range("A1" ,Range("A1").Offset(3,0)).Select
You need a small VBA procedure to go down a column and delete the entire row if the cell is empty. You need a small macro to go down a column and delete the entire row if the value of the cell is 0. See both and 20 more of the same nature in "vba-short-macros-1.xls"
End
Here is something that you will use at the beginning of yur programming career but that you will stay away from once you have started working with variables:
Range("A38").End(xlDown).Select will take you to the last cell with a value in column A
Range("A38").End(xlUp).Select will take you to the first cell with a value in column A
Range("A38").End(xltoRight).Select will take you right to the last cell with a value in row 38
Range("A38").End(xltoLeft).Select will take you left to the first cell with a value in row 38
Note: End will work if there are no empty cells within the row or the column.
Note: Notice the "to" before left and right. (xltoLeft, xltoRight)
Note: See the section "Precautions and Ideas" below on End
Value
When you want to enter a numerical value in a cell you will write:
Range("A1").Value = 32
Note that you don't need to select a cell to enter a value in it you can do it from anywhere on the sheet. For example from cell G45 you can write:
Range("A1").Value = 32
You can even change the value of cells on another sheet with:
Sheets("SoAndSo").Range("A1").Value = 32
You can also enter the same value in many cells with:
Range("A1:B32").Value = 32
If you want to enter a text string in a cell you need to use the double quotes like:
Range("A1").Value = "Peter" will return Peter. Notice the quotation marks.
If you want to enter a text with double quotes showing in the cell you need to triple the double quotes like:
Range("A1").Value = """ Peter""" will return "Peter" with quotation marks
Formula
When you want to enter a formula in a cell you will write:
Range("A1").Select
Selection.Formula = "=C8+C9"
Note the two equal signs (=) including the one within the double quotes like if you were entering it manually.
Again you don't need to select a cell to enter a formula in it, from anywhere on the sheet you can write:
Range("A1").Formula = "=C8+C9"
If you write the following:
Range("A1:A8").Formula = "=C8+C9"
The formula in A1 will be =C8+C9, the formula in A2 will be =C9+C10 and so on. If you want to have the exact formula =C8+C9 in all the cells, you need to write:
Range("A1:A8").Formula = "=$C$8+$C$9"
In these instances, I create the formula directly in a cell and copy/paste it between parentheses in my VBA code. In Excel you discover how easy to add dollar signs to addresses with the F4 key.
See example 4 below to see how to make adresses variable within a formula
Copy/Paste
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.
Range("A1:A5").Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode=False
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
Font.Bold
To make the font bold or not:
Range("A1").Font.Bold=True
Range("A1").Font.Bold=False
CurrentRegion
One of the most important property when working with sets of data and databases:
Range("A1).CurrentRegion.Select
will select all cells from A1 to the first empty row and the first empty column. See the lesson on databases in the tutorial on Excel at www.excel-examples.com to discover the importance of CurrentRegion.
Cells is all the cells of a worksheet as CurrentRegion is all the cells of a database (from where it is to the first empty column on the right ad the first empty row at the bottom. To see what Excel recognizes as a database see the website on Excel
Columns and Column
To select a single column you will write:
Columns("A").Select
Notice than Columns is plural even if you are selecting only one column.
To select a set of contiguous columns you will write:
Columns("A:B").Select
A special use of Range
To select a set of non contiguous columns you will not write:
Columns("A:A,C:C,E:E").Select
but:
Range("A:A,C:C,E:E").Select
Column (no "s") will be used in a line of code like the following:
var=Activecell.Column will store the column number in a variable named var
Activecell.Column cannot be use by itself
var=Selection.Column will store the column number of the Activecell of the selection in a variable named var
See below to learn about Activecell and Selection and see in Level 2 how to use this property with a variable
Rows and Row
To select a single row you will write:
Rows("1").Select
Notice than Rows is plural even if you are selecting only one row.
To select a set of contiguous rows you will write:
Rows("2:3").Select
A special use of Range
To select a set of non contiguous rows you will not write:
Rows("1:1,3:3,5:5").Select
but:
Range("1:1,3:3,5:5").Select
Row (no "s") will be used in a line of code like the following:
var=Activecell.Row will store the row number in a variable named var
Activecell.Row cannot be use by itself
var=Selection.Row will store the row number of the Activecell of the selection in a variable named var
See below to learn about Activecell and Selection and see in Level 2 how to use this property with a variable
EntireColumn, EntireRow
You can also select the column or the row with this:
ActiveCell.EntireColumn.Select
ActiveCell.EntireRow.Select
Range("A1").EntireColumn.Select
Range("A1").EntireRow.Select
If more than one cell is selected the following code will select all rows and columns covered by the selection:
Selection.EntireColumn.Select
Selection.EntireRow.Select
Hidden
To hide and unhide
Columns("A").Hidden=True
Columns("A").Hidden=False
Rows("1").Hidden=True
Rows("1").Hidden=False
NOTE: Remember to use the Macro Recorder to learn more code on this topic or any other.
Lesson 2-6: Assisted
1- 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:
Range("A1:A5").Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode=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.
1A- 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.
2- Also use the MR 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
Use the MR to format as text the code is:
Selection.NumberFormat= "@"
Lesson 2-6: Precautions and Ideas
Cells or Ranges
A lot of VBA beginners start their career using Cells. For example:
Cells(1,1).Select is the same thing as Range("A1").Select and
Cells(11,31).Select is the same as Range("AE11").Select.
I strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes the code much clearer and your not forced to remember that column AE is column 31.
The only time that you will use Cells is when you want to select all the cells of a worksheet like in:
Cells.Select
To select all cells and then
To empty all cells of values or formulas you will use:
Cells.ClearContents
End
If there is only one line of data in your sheet and your code is:
Range("A1").End(xlDown).Select
You will end up in cell A65536 and you will have created a monster workbook.
If there is only one column of data in your sheet and your code is:
Range("A1").End(xltoRight).Select
You will end up in cell IV1 and you will have created a monster workbook.
So learn rapidly about counting rows and columns and storing the number in a variable like "varNbRows" to use:
Range("A1").Offset(varNbRows,0).Select
Select and Selection
The object Selection comprises what is selected. It can be a single cell, many cells, a column, a row, many of these, a chart, an image or any other object.
All the methods and properties that apply to Cells, Range, Rows or Columns also apply to Activecell and Selection
For example:
Range("A1:A30").Select
Selection.ClearContents
will remove the contents (values or formulas) of the cells A1 to A30.
Acting without Selecting
From anywhere on the sheet you can write orders about other cells or ranges. For example you can write:
Range("A1:A30").ClearContents
instead of
Range("A1:A30").Select
Selection.ClearContents
You can also from cell G5 write:
Range("A1").Value = 32
Range("A1").Formula = "=F32+F33"
But BEWARE. When moving down one cell after the operation with:
Selection.Offset(1,0).Select
will get you to G6 and not to A2 because the ActiveCell is G5 and not A1 that has never been selected. So you can reduce the number of code lines avoiding the Select/Selection thing but there are consequences.
ActiveCell
The ActiveCell is the selected cell or the first cell of the range that you have selected.
All the methods and properties that apply to Cells, Range, Rows or Columns also apply to Activecell and Selection
Try this in Excel:
- select column A and see that cell A1 is not highlighted as the other cells. It is the ActiveCell.
- select row 3 and see that A3 is the ActiveCell.
- select cells D3 to G13 starting with D3 and see that D3 is the ActiveCell
- now select the same range but start with G13. So select G13 to D3 and see that G13 is the ActiveCell
The ActiveCell is a very important concept that you will need to remember as you start developing more complex procedures.
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.
Error Message
You will often see the following error message when you code for Cells, Ranges, 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.
Lesson 2-6 : Level 2
Addresses
If you name some fields (myField) you must still use the quotation marks within the parentheses:
Range("myField").Select
But if you use the content of a variable as address don't use the quotation marks:
varAddress = "A12"
Range(varAddress).Select
You can also use an address that is in another cell of another sheet. For example let's say that in cell A1 of the sheet "Data" you have entered C12. The following code:
Range(Sheets("Data").Range("A1").Value).Select
will take you to cell C12.
Use the same approach for Rows and Columns
Advanced examples on Ranges, Columns and Rows
1- If you are addong data to a database the first empty line is:
You are adding lines of data to an excel database. You want each new record to be pasted in the first empty line. You first go to the title cell of the first column, you count the number of lines using a variable, you offset and you paste:
Range("A1").Select
Activecell.Offset(Activecell.CurrentRegion.Rows.Count,0).Select
2- You want to select a block of cells (3 by 3) from cell A1
Range("A1",Range("A1").Offset(2,2)).Select
Notice that you offset 2 to get 3
You want to do the same thing from the cell that is selected:
Range(Activecell,Activecell.Offset(2,2)).Select
Often you will get the following error message when you try to write such a sentence:
and it is because you are forgetting the second parenthesis before Select.
3- Sometimes part pf the formula must vary depending on which cell is selected. You need the line number of a formula to be variable. For example in cell G8 the formula must be "=F8", in cell G9 the formulas must be ""=F9". Try this code:
Activecell.Formula= "=F" & Activecell.Row
replacing the row number by Activecell.Row and 2 ampersands. Notice also that each segment of the rest of the formula is encased in quotation marks.
If when the selected cell is G8 you want the formula to look on row up "=F7" you will use:
Activecell.Formula= "=F" & Activecell.Row - 1
adding -1 between the ampersands
The entire address can also vary. When in cell G8 the formula should be "=SUM(G1:G7)". You will write:
Activecell.Formula= "=SUM(G1:" & Activecell.Offset(-1,0).Address & ")"
4- Text strings within formulas
Notice in the formula above that when you want to use a string within a formula you must double the quotation marks like in ""Sold"".
Hence if you want to check if cell A1 is empty you would not write:
Range("B1").Formula= "=IF(A1="",1,0)" but
Range("B1").Formula= "=IF(A1="""",1,0)"
This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple