Relative Selection using Excel VBA Macros
Part six 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
  6. Selecting Entire Columns or Rows in Excel VBA macros (this blog)

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 Entire Columns or Rows

The EntireColumn and EntireRow properties allow you to turn a block of cells into the columns or rows containing them.

The macros in this blog entry mimic the short-cut keys SHIFT + SPACE BAR (to select rows) and CTRL + SPACE BAR (to select columns).

As an example, suppose that you wanted to use a macro to automatically resize columns B and C below to accommodate the widest text in them:

Two columns needing autofit Two columns after autofit
We want to go from this ... ... to this

The command to do this could be as follows:

Sub ChangeColumnWidths()

'resize columns corresponding to current cells


End Sub

You could use a similar method to resize the rows corresponding to the current selection.


If you can think of any other relative selection or absolute selection techniques I've missed out, let me know.  Otherwise, happy coding!

This blog has 0 threads Add post