should work from anywhere in the active workbook
Sub gothereandandnameoffsetcell()
Application.Goto "gothere"
ActiveCell.End(xlDown).Name = "wentthere"
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"jeff" <(E-Mail Removed)> wrote in message
news:33c502e0-3bcc-4c39-9d6c-(E-Mail Removed)...
On Apr 16, 12:03�pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Dim myRng as range
> Application.Goto Reference:="DateColumn1"
> set myrng = selection.end(xldown)
> myrng.name = "TempGas1"
>
> This is just a single cell, right?
>
> You could use:
> selection.end(xldown).name = "TempGas1"
>
> But I like using a range variable -- just in case the range gets more
> complex
> and I need to modify it.
>
>
>
>
>
> jeff wrote:
>
> > I've tried using the recorder, but it gives the exact cell location. I
> > don't want that. I want the range name to be created where ever the
> > active cell might be.
> > Here's what I got when I used the recorder:
>
> > Application.Goto Reference:="DateColumn1"
> > � � Selection.End(xlDown).Select
> > � � Range("A22").Select
> > � � ActiveWorkbook.Names.Add Name:="TempGas1", RefersToR1C1:="='V 3'!
> > R22C1"
>
> > I don't want the part
> > RefersToR1C1:="='V 3'!R22C1"
> > in the coding, but it doesn't work without it. The way it shows above,
> > it goes to an exact cell location.
>
> > What I want in that last line is to name the range where ever the
> > active cell is.
> > Nothing else.
> > Can anyone help?
> > Thanks
> > jeff
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -
Thanks Dave
I Googled it, and found another solution that also seems to work.
Application.Goto Reference:="DateColumn1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="TempGas1", RefersTo:=ActiveCell
I guess I was trying to get it without the RefersTo part of the
statement. I didn't know you could just put ActiveCell in there.
Yes, this will only be 1 cell in the named range.
Your solution works, also.
I appreciate your help.
jeff
Similar Threads
