Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Module 2 - Moving Around in Excel

In this module you'll learn how to select cells and ranges in Excel, and how to move around a worksheet.

2.1 - Basic Cell Selection
2.1.1 - Selecting Single Cells
2.1.2 - Selecting Multiple Cells
2.1.3 - Moving Between Worksheets
2.1.4 - Moving Between Workbooks
2.1.5 - Moving vs. Referencing

2.2 - Working with Ranges
2.2.1 - The End Property
2.2.2 - Selecting an Entire List
2.2.3 - The Offset Property
2.2.4 - Building a List

Choose what you want to learn from the list of lessons above.

This page provides a brief summary of what you've learned in this module. You can click here to download the example code shown below.

Basic Cell Selection

Selecting Single Cells

You can select a single cell by passing its reference into the Range property and applying the Select method.

'Select A1

Range("A1").Select

You can refer to the selected cell using the ActiveCell property.

'Change the value of the selected cell

ActiveCell.Value = "Wise Owl"

You can refer to a single cell using the Cells property.

'Select B3

Cells(3, 2).Select

Selecting Multiple Cells

You can refer to a block of cells using the Range property.

Range("A1:D1").Select

You can refer to a block of selected cells with the Selection property.

Selection.Interior.Color = rgbBlue

Moving Between Worksheets

You can move to another worksheet by passing its name to the Worksheets property and applying the Select method.

Worksheets("Sheet2").Select

You can refer to the currently selected sheet with the ActiveSheet property.

Range("A1").Value = ActiveSheet.Name

You can refer to a worksheet based on its position from left to right.

Worksheets(3).Select

Moving Between Workbooks

You can move to another workbook by passing its name to the Workbooks property and applying the Activate method.

Workbooks("Book2.xlsx").Activate

You can refer to the workbook in which your code is stored with the ThisWorkbook property.

ThisWorkbook.Activate

Moving vs Referencing

You can modify properties of a range without needing to select it first.

Range("B3").Value = "Wise Owl"

You can modify cells on another worksheet without needing to select the worksheet.

Worksheets("Sheet2").Range("A1").Value = "Wise Owl"

You can modify cells in another workbook without needing to activate the workbook.

Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = "Wise Owl"

Working with Ranges

The End Property

You can use the End property to find the edge of a block of populated cells.

ActiveCell.End(xlDown).Select

ActiveCell.End(xlToRight).Select

ActiveCell.End(xlUp).Select

ActiveCell.End(xlToLeft).Select

To find the bottom of a list which has empty cells, it's perhaps better to go up from the bottom of a worksheet.

Range("A1048576").End(xlUp).Select

Selecting an Entire List

You can use the End property to select from the top to the bottom of a list.

Range("A1", Range("A1").End(xlDown)).Select

You can use the End property to select from the top left to the bottom right of a list.

Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select

You can use the CurrentRegion property to refer to an entire table.

Range("A1").CurrentRegion.Select

The Offset Property

You can use the Offset property to move up, down, left and right on a worksheet.

'move down

ActiveCell.Offset(1, 0).Select

'move up

ActiveCell.Offset(-1, 0).Select

'move right

ActiveCell.Offset(0, 1).Select

'move left

ActiveCell.Offset(0, -1).Select

Building a List

You can combine the End and Offset properties to reach the next blank row in a list.

Range("A1").End(xlDown).Offset(1, 0).Select

This page contains reference material for the code used in this module.

The End Property

The End property of a Range object allows you to use one of four constants to specify the direction:

Constant
xlDown
xlToLeft
xlToRight
xlUp

Try doing one or more of the following exercises for this module:

Exercise 2.01 Exercise 2.02

There is currently no test for this module.

This page has 0 threads

Add a new post

MODULES

LESSONS

Basic Cell Selection

Selecting Single Cells

Selecting Multiple Cells

Moving Between Worksheets

Moving Between Workbooks

Moving vs Referencing

Working with Ranges

The End Property

Selecting an Entire List

The Offset Property

Building a List

The End Property

Head office

London

Manchester