Relative Selection using Excel VBA Macros
Part three 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 (this blog)
  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 to the End of a Block

Suppose now that you want to select from Winnie the Pooh down to the last character in the Hundred Acre Wood:

Arrow showing select down

We want to select from the green cell all the way down to the bottom of the block.


Clearly one way to do this would be to offset 6 cells down:

Sub SelectLastCharacter()

'select from Pooh down to bottom of block

ActiveCell.Offset(6, 0).Select

End Sub

The problem with this approach is that it wouldn't work if we had a different character selected, nor if we add more characters to the list.  What we need to do is to go to the last non-blank cell in the block.

Using END

Fortunately, that's exactly what the End property does:

Showing 4 arrows, one in each direction

From any cell you can move in one of 4 directions: up, down, left and right.


When you start typing in a command, Excel will suggest the 4 possible directions:

Showing autocompletion for the END method

The 4 possible directions for END

Thus for our example, the line of code you'll need is:

Sub SelectLastCharacter()

'select from Pooh down to bottom of block


End Sub

Combining END Properties

If you add one END property on to the other, it can be a quick method of getting to the bottom right corner of a block:

Sub SelectLastCharacterScore()

'select from Pooh down to bottom right corner of block


End Sub

Thus if you select Pooh Bear's cell B5 and then run the above macro, you'll get the following:

Showing end down and right

Beginning with the green cell selected, you'll end up with the orange one, following the arrows.


What Happens if you're at the End of a Block

One thing to watch out for is that you might be at the end of a block when you run an End command.  If this is the case, expect the unexpected!

Bottom of block Last row of sheet
If you start here and go down to the end of a block ... ... don't be surprised if you end up on the last row of your sheet!

To get round this, you could consider checking the cell beneath before you go to the end of your block:

Sub SelectLastCharacter()

'select to bottom of block (if not already there)

'if the cell beneath the current one isn't empty ...

If ActiveCell.Offset(1, 0).Value <> "" Then

'... go down to the last cell in the block


End If

End Sub

The above macro will have a quick peek at the cell below the current one, and only when it's sure that this cell is non-empty will it go to the bottom of the block.

This blog has 0 threads Add post