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.

  1. Selecting Cells with Excel VBA Macros - Relative (this blog)
  2. Moving Around a Spreadsheet in VBA - Offset
  3. Selecting to the End of a Block in Excel VBA macros
  4. Selecting a Block of Cells using Range
  5. Resizing Blocks of Cells in Excel VBA macros
  6. 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.

Posted by Andy Brown on 16 August 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

  1. Go down 3 cells;
  2. Go to the end of the current block of cells;
  3. 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.

A block of cells selected

In this case:

 

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.

 

This blog has 0 threads Add post