VBA Excel — Cells, Ranges and Offset

Cells

To reach to a particular cell you need to tell to compiler the cell is located in which row and which column.

Example: in VBA Excel when you say Cells(2,1) which means, in 2nd row and in 1st column means “A2”

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 AD is column 30.

The only time that you will use Cells is when you want to select all the cells of a worksheet. For example:

Cells.Select

To select all cells and then empty all cells of values or formula you will use:

Cells.ClearContents

Range

A range refers to a cell or a range of cells. A range can be of three types:

One cell (Single Cell)

Multiple cells

Multiple non-contiguous cells – the same as using with CTRL + Clicking cells

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

Offset

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 up  (from B2 to B1):

Range(“B2”).Offset(-1,0).Select

To move one cell to the left  (from B2 to A2):

Range(“B2”).Offset(0,-1).Select

To move one cell down from the selected cell:

ActiveCell.Offset(1,0).Select

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:

Range(“A1”).Offset(5,6).Select

 

 

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/05/14/vba-excel-cells-ranges-offset/
YouTube31
YouTube