EXCEL VBA How to activate last cell in a range? 2

[cameronfraser]

In EXCEL VBA I have a named range and I want to activate the last cell in the range.

Example:
the EXCEL named range "Dog" is "B6:B25" I want to activate the last cell in the range, "B25". I know Range("B25&quot will select the cell, but when rows are inserted above "Dog" then Range("B25&quot will not select the last cell of the range.
I tried the command:

Range("Dog&quot.SpecialCells(xlCellTypeLastCell).Activate

Thid Does not work because this selects the last cell in the used range, not the specified range.

Is there a simple way to find the last cell in the range?

 

[acron]

This may appear convoluted, but will work :

Range("Dog&quot.Cells((Range("Dog&quot.Cells.Count)).Activate

or abbreviate to :

[Dog].Cells(([Dog].Cells.Count)).Activate

A.C.

 

[acron]

You can abbreviate further with :

[Dog].Cells(([Dog].Count)).Activate

A.C.

 

[SkipVought]

Hi, cameronfraser,
Here's some info you can get from your range...

    With [Dog]
        lFirstRow = .Row
        lRowCount = .Rows.Count
        iFirstCol = .Column
        iColCount = .Columns.Count
    End With
    lLastRow = lFirstRow + lRowCount - 1
    iLastCol = iFirstCol + iColCount - 1

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Loomah]

OR
[dog].End(xlDown).Activate
Is this shorter than acron's reply?
:-D

 

[acron]

Loomah,

It might be shorter but will only work if there are no blank cells in the range.

A.C

 

[Loomah]

Ah Acron
Not only will it only work if there are no blank cells but it will only work if the range is a single column!

Fools rush in...
I'll get me coat

 

[cameronfraser]

Thanks for the solutions.

I have a question. What do the [ ] around Dog mean?

Range("Dog&quot.Cells((Range("Dog&quot.Cells.Count)).Activate

or abbreviate to :

[Dog].Cells(([Dog].Cells.Count)).Activate

 

[acron]

The [] brackets are shorthand for an EVALUATE function, and in this instance they evaluate the expression Dog to Range("Dog&quot.

It's also useful when working with Worksheet functions in VBA, e.g (a trivial example)?

Application.WorksheetFunction.Sum(Range("A1:A10&quot)

becomes

[Sum(A1:A10)]

A.C

 

[Loomah]

Acron
I've learned something here re [] I've always known they were an option but never what they actually meant!

I also have a question tho' Are the double brakets required? ie Cells(([Dog].Cells.Count))

It makes no difference to whether the code works or not but I wondered if this was something else I wasn't aware of. VBA is just something I'm doing to kill time at the moment!

 

[acron]

Loomah,

Sorry, no the double brackets are not required - I cannot imagine how I managed to include them.

A.C

 

[cameronfraser]

I thought the (( )) brackets was intended to make that portion of the line stand out.

Range("Dog&quot.Cells((Range("Dog&quot.Cells.Count)).Activate

Range("Dog&quot.Cells(Range("Dog&quot.Cells.Count).Activate

The first line was just a little easier to understand.

Also,
EVALUATE function. I like it. I looked in my two EXCEL VBA books and it wasn't mentioned. The VBA online help did have it though.

Acron, thanks for the help. One Gold Star for you.