Defining dynamic named ranges with VBA....

[dan_l]

I make about 30 or so dynamic ranges for charting purposes. The series are predictable. Like:


=OFFSET('BLG'!$C$7,0,0,1,COUNT('BLG'!$C$7:$X$7))

=OFFSET('BLG'!$C$8,0,0,1,COUNT('BLG'!$C$8:$X$8))

=OFFSET('BLG'!$C$9,0,0,1,COUNT('BLG'!$C$9:$X$9))


I could just do it by hand, but I wonder if there isn't a VBA solution that would just create all 30 DNR's for me. I found this:


http://www.vbaexpress.com/kb/getarticle.php?kb_id=157


Sub NamedRange()


Dim Rng1 As Range


'Change the range of cells (A1:B15) to be the range of cells you want to define

Set Rng1 = Sheets("Sheet1").Range("A1:B15")

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1


End Sub


Now I know if I use application.offset it will just return a range with the exact size as is currently on the sheet. That won't update until the macro refreshes (which isn't practical for this project). Typically, in a situation where I needed dynamic in a cell I wouldn't do cell.value = application.sum(whatever), I would do cell.value ="=sum(whatever)".


So could I do:


for ticker 1 to 10

set rng1 = "=OFFSET('BLG'!$C$"&ticker&",0,0,1,COUNT('BLG'!$C$"&ticker&":$X$"&ticker&"))"


ActiveWorkbook.Names.Add Name:="Range"&ticker, RefersTo:=Rng1

next ticker


Or am I crazy?

 

[Luke M]

Close. You're not using a Range however, it's just a string. So your macro would look like:

Sub NamedRange()


Dim Rng1 As String


'Change the range of cells (A1:B15) to be the range of cells you want to define

For ticker = 1 To 10

Rng1 = "=OFFSET('BLG'!$C$" & ticker & ",0,0,1,COUNT('BLG'!$C$" & ticker & ":$X$" & ticker & "))"

ActiveWorkbook.Names.Add Name:="Range" & ticker, RefersTo:=Rng1

Next ticker


End Sub

Couple thoughts...You could do this strictly in the workbook, using some concatenation to build the text strings, and then use Insert - Name - Create, to get the named ranges. Also, and this is more preference, might it be easier to have one named range be:

=OFFSET('BLG'!$C$7,0,0,1,COUNT('BLG'!$C$7:$X$7))

and then all the others can simply be something like:

=OFFSET(Range1,1,0)


That way if you ever have to change the cell references, you're only change the one. Elaborating on this, unless you're using all the named ranges at same time, perhaps you could do something like:

=OFFSET(Range1,[make this a variable],0)

and your user controls which range to look at?

 

[dan_l]

Luke, thanks. I did it with the offset(range,1,0) method. I had no idea you could do that.

 

[Luke M]

You're welcome Dan, always glad to help. =)