Named Range Macro

[RoyinUK2000]

I have tried to make a macro that will look if any data has been added to a worksheet and make the whole data selection a named range, but a novice like me cannot get this to work.

My code is as follows:-
Sub NameRange()
Range("A1").Select
Selection.End(xlToRight).Select
addy1 = Selection.Address(ReferenceStyle:=xlR1C1)
Selection.End(xlDown).Select
addy2 = Selection.Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.Names.add Name:="Range1", RefersToR1C1:="=Drumming Details!" & addy1 & ":" & addy2
End Sub


Anyone help a newbie out here would be most appreciated.


Ta
Roy

 

[Richie(UK)]

Hi Roy,

How about just referring to the usedrange?

<pre>
Sub NameRange()
Dim addy1 As Range
Set addy1 = ThisWorkbook.Sheets(1).UsedRange
ThisWorkbook.Names.Add Name:="Range1", RefersTo:=addy1
End Sub

</pre>

HTH

 

[Andrew Poulsom]

Try this:

Sub NameThatRange()
    Sheets("Drumming Details").Range("A1").CurrentRegion.Name = "Range1"
End Sub