Moving around the Worksheet in VBA for Excel

Selection, Activecell, CurrentRegion and other special addresses

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.

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.

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

End

Here is something that you will use at the beginning of your 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

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

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.

Discover more tips
and practical examples of real solutions to real problems
with step by step development instructions in the 25

Resource Spreadsheets