VBA Lesson 16: Cells, Ranges, Columns and Rows in VBA for Excel
This is an excerpt from Pierre Leclerc (www.excel-vba.com)
A lot of VBA beginners start their career using Cells. For example:
We strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are 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. For example:
To select a single cell you will write:
To select a set of contiguous cells you will use the colon and write:
To select a set of non contiguous cells you will use the comma and write:
To select a set of non contiguous cells and a range you will use both the colon and the comma:
The Offset property is the one that you will use the most with Range to move around the sheet.
To move one cell down (from B2 to B3): Range("B2").Offset(1,0).Select
To move one cell down from the selected cell:
As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need:
You will use very often the following piece of code . It selects a cell and 4 more to the right to be copied/pasted somewhere else:
Go to the next lesson