- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
RELATIVE SELECTION USING EXCEL VBA MACROS
Part one of a six-part series of blogs
This blog shows the various techniques within Excel Visual Basic for moving around and selecting cells relative to the active cell or selection. The blog includes a discussion of the Offset, End, Resize, Range, EntireRow, EntireColumn and CurrentRegion methods.
- Selecting Cells with Excel VBA Macros - Relative (this article)
- Moving Around a Spreadsheet in VBA - Offset
- Selecting to the End of a Block in Excel VBA macros
- Selecting a Block of Cells using Range
- Resizing Blocks of Cells in Excel VBA macros
- Selecting Entire Columns or Rows in Excel VBA macros
This blog is part of our Excel macros online tutorial series. Wise Owl also run VBA macros courses for beginners and advanced users.
Selecting Cells with Excel VBA Macros - Relative
Another blog has already explained how to select cells in absolute mode; this one shows how to select cells relative to your starting point. Examples of relative selection would be:
- Go down 3 cells;
- Go to the end of the current block of cells;
- Select from this cell down to the bottom of the block.
The VBA commands given in this blog work for Excel 2010, Excel 2007, Excel 2003 and (as far as I remember) Excel 2002, 2000 and 97 too!
If you want to try out any of the macros listed in this blog, you can download the file of Winnie the Pooh characters.
ActiveCell and Selection
Before we begin, there are two keywords which you must understand in Excel Visual Basic for Applications (VBA): ActiveCell and Selection.
In this case:
- The active cell (written as ActiveCell) s B5; but
- The selection (written as Selection) is B5:C11.
Every worksheet has an active cell, and when only one cell is selected, the active cell and selection are the same thing.
Now that we've learnt about the two key Excel VBA concepts of ActiveCell and Selection, let's look at how you can move round a spreadsheet.
RELATIVE SELECTION USING EXCEL VBA MACROS
Part one of a six-part series of blogs
This blog shows the various techniques within Excel Visual Basic for moving around and selecting cells relative to the active cell or selection. The blog includes a discussion of the Offset, End, Resize, Range, EntireRow, EntireColumn and CurrentRegion methods.
- Selecting Cells with Excel VBA Macros - Relative (this article)
- Moving Around a Spreadsheet in VBA - Offset
- Selecting to the End of a Block in Excel VBA macros
- Selecting a Block of Cells using Range
- Resizing Blocks of Cells in Excel VBA macros
- Selecting Entire Columns or Rows in Excel VBA macros
This blog is part of our Excel macros online tutorial series. Wise Owl also run VBA macros courses for beginners and advanced users.
Comments on this blog
This blog currently has no comments.