WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
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 (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.

Don't forget that there are many more resources for learning Excel/VBA either at our Excel training home page or VBA home page.

This blog has 1 thread Add post
25 May 17 at 18:01


I would appreciate if you could help me with the following code:

Private Sub ComButSave_Click()

Dim NumberOfCells As Long
    Range("A5:OI5").Find(What:=TxtBxMapNumber.Value, MatchCase:=True).Select
    NumberOfCells = Range(ActiveCell, ActiveCell.End(xlDown)).Count
    With ActiveCell
        .Offset(NumberOfCells, 0).Value = ComboBoxNames.Value
        .Offset(NumberOfCells+1, 1).Value = TxtBxDateReturned.Value
    End With
End Sub


The idea is that when I click the Userform button the code: 

1) find's a specific number from a range, 

2) selects the cell where the number is,

3) counts the number of cells between the selected cell and the last cell, and 

4) places specific values to the next cell below the activecell (according to the counted number)

Why my code  has a Run-time Error '1004' (Application defined or Object defined error) when reaches the Offset line of code?

Thank you once more for your valuable help.


25 May 17 at 20:15

Hi Costas

I suspect the problem is that you're trying to go beyond the bottom of the worksheet.  Put a breakpoint on this line of code (as I'm sure you know, you can press F9 to do this):

.Offset(NumberOfCells, 0).Value = ComboBoxNames.Value

When you run your macro, let your mouse hover over the NumberOfCells variable, to see what its value is.  My suspicion is that it's returning a very large number, as you were already at the bottom of a block.