Moving around the Worksheet in VBA for Excel
Cells and Range in VBA for Excel
In Part 1 we will study theVBA word that you will use the most: Range.
Cells or Range
A lot of VBA beginners start their career using Cells. For example:
I 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 like in:
See part 3 of lesson 2-6 to learn why you should never use Delete but ClearContents and all about Activecell, Selection and CurrentRegion
Note the ever present set of quotation marks in the following lines of code. You will be using the quotation marks with range, rows, columns, worksheets, workbooks and many other objects.
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:
A special use of Range
To select a set of non contiguous rows you will not write:
BEWARE: You cannor write:
For advanced users: Instead of using addresses like A1 or G27 with Range you can use named fields, addresses that you have stored in other cells or variables to replace addresses or part of them like in the sentences below.
Note: Remember that if your sentence reads:
To make the font bold or not: