Relative Selection using Excel VBA Macros
Part five 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
  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 (this blog)
  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.

Resizing Blocks of Cells

You can use either the Range property (within a command) or the Resize property to change the height and width of blocks of cells.

These techniques aren't that commonly used, so if you've read thus far and are feeling proud of yourself but a little tired, now's a good time to stop!

Using the Range Property within a Command to Resize

The way this method works is by redefining the coordinate system temporarily to make the active cell the top left corner.  This is easier to explain in diagrams!

Suppose that with Pooh Bear (inevitably) selected, we want to select the block of cells shown:

Block of cells to select

We want to resize the green cell so that it begins on B5, and carries on down and across to C11.


If you consider the Pooh Bear cell to be the top left corner of another virtual spreadsheet, we actually want to select range A1:B7:

Range with temporary coordinates shown

If the Pooh Bear cell were cell A1, then the area we'd be selecting is shown shaded.


Given this rather odd way of looking at the world (or at least, at the Excel part within it), the command to make this work is as follows:

Sub ResizeBlock()

'given Pooh Bear as the top left corner of the world, select rest of block


End Sub

Using Resize to Change the Height and Width of a Block of Cells

You could also solve the above problem using the Resize property:

Sub ResizeBlock()

'given Pooh Bear selected initially, change height and width of selection

ActiveCell.Resize(7, 2).Select

End Sub

The arguments this property takes are:

  • The number of rows in the final selection; and
  • The number of columns in the final selection.

While conceptually this is fairly easy to understand, it's hard to think when it might be a useful command to run!


This blog has 0 threads Add post