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:
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 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:
Cells.Select
To select all cells and then to empty all cells of values or formulas you will use:
Cells.ClearContents

See part 3 of lesson 2-6 to learn why you should never use Delete but ClearContents and all about Activecell, Selection and CurrentRegion

Range

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:
Range("A1").Select

To select a set of contiguous cells you will use the colon and write:
Range("A1:G5").Select

To select a set of non contiguous cells you will use the comma and write:
Range("A1,A5,B4").Select

To select a set of non contiguous cells and a range you will use both the colon and the comma:
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

BEWARE: You cannor write:
Sheets("SOandSO").Range("A1").Select
You  must first go to the sheet then select the range
Sheets("SOandSO").Select
Range("A1").Select

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.
Range("myNamedField").Select
Range(myVariable).Select notice the absence of quotation marks
Range(Range("B2").Value).Select
Range("A" & Range("B2").Value).Select
Range("A" & myVariable).Select
Range( myVariable & "12").Select
Range("A12:" & Range("B2").Value).Select
Range("A12:" & myVariable).Select
See part 5 of lesson 2-6 for examples.

Note: Remember that if your sentence reads:
Range("C12").Select
and you insert a line above line 12 your macro doesn't work anymore because what was in cell C12 is now in cell C13..

Font.Bold

To make the font bold or not:
Range("A1").Font.Bold=True
Range("A1").Font.Bold=False

Learn More
At your own pace
In the confort of your home and office