Identifying first & last cell in a selected range

[daveh]

Hi,

I want a user to select a range on the worksheet, and then to click a button to redefine that range as a new defined name. The range has a list of job roles, with a defined range name of "jobroles". I have created the following code which allows them to redefine the name after they make any changes to the list.

Sub performingsector()
'
' performingsector Macro
' Macro recorded 05/06/2005 by IBM_USER
'

    ActiveWorkbook.Names("jobroles").Delete
    
    firstcell = ActiveCell.Address
    firstrow = ActiveCell.Row
    firstcol = ActiveCell.Column
    Selection.End(xlDown).Select
    lastcell = ActiveCell.Address
    lastrow = ActiveCell.Row
    lastcol = ActiveCell.Column
    
    firstcell = Replace(firstcell, "$", "")
    lastcell = Replace(lastcell, "$", "")
    
    Range(firstcell, lastcell).Select
        
    ActiveWorkbook.Names.Add Name:="jobroles", RefersToR1C1:="='Job Roles'!R" & firstrow & "C" & firstcol & ":R" & lastrow & "C" & lastcol & ""

'
End Sub

This all works fine except for one bit. In order to identify the last cell in the range that they selected, I am using "Selection.End(xlDown).Select". However, this doesn't always have the desired effect, as it simply selects the entire list as low down as it goes, regardless of what they have selected.

So I have two questions. Firstly, is it possible to better identify the last cell in the range they have selected. Secondly, which would also answer the first - is there a better way than this to identify the first and last cell in their selected range, regardless of where they started selecting it (e.g. A1-C5 even if they started highlighting from C5 up to A1).

Thanks in advance,
David.

 

[TonyJollans]

Hi David,

You can simply do:

[blue]ActiveWorkbook.Names.Add Name:="jobroles", RefersTo:=Selection[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[CautionMP]

[tt]Sub performingsector()
'
' performingsector Macro
' Macro recorded 05/06/2005 by IBM_USER
' Macro modified 06/05/2005 by CautionMP
'

ActiveWorkbook.Names("jobroles").Delete
ActiveWorkbook.Names.Add "jobroles", ActiveCell.CurrentRegion
End Sub[/tt]

 

[daveh]

Hi Tony/CautionMP,

Excellent, thanks - I thought it must be possible to do this, but couldn't find any details of it!

Thanks for your help!
Take care,
David.