This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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:

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:

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

ActiveCell.End(xlDown).Select

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

ActiveCell.End(xlDown).End(xlToRight).Select

End Sub

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

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!


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

ActiveCell.End(xlDown).Select

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.

Parts of this blog
Selecting Cells with Excel VBA Macros - Relative Moving Around a Spreadsheet in VBA - Offset Selecting to the End of a Block (this blog) Selecting a Block of Cells using Range Resizing Blocks of Cells in Excel VBA macros Selecting Entire Columns or Rows in Excel VBA macros

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	Costas
When:	25 May 17 at 18:01

Reply to Costas

Hi,

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.

Costas

From:	Andy B
When:	25 May 17 at 20:15

Reply to Andy B

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.

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Selecting to the End of a Block

Using END

Combining END Properties

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

Head office

London

Manchester