In this module you'll learn how to select cells and ranges in Excel, and how to move around a worksheet.
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 |
There is currently no test for this module.