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

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

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