Automatically define series of named ranges
 

I have a sheet that has several hundred rows. I would like to start at
row 1, select the range from 1 to 13, create a named range from it,
then move to row 2 and repeat, throughout the sheet, until there are
no more 13 row ranges possible. How on earth would I go about doing
this?

Automatically define series of named ranges
 

Hi Jon

Sub DefineCalcRanges()
Set Shd = Sheets("Data")
'column ranges for columns 4 to 30
For i = 1To 200 'number of rows required
Set rng = range(Shd.Cells(i, 1),Shd.Cells(i,13))
rng.Name = "rname" & i
Next i
End Sub

produces
rname1 for Range("A1:M1") on Sheets("Data")
rname2 for Range("A2:M2")
rname3 for Range("A3:M3")
and so on

hope this helps

Andrew Bourke



"Jon Tillman" wrote in message
...
I have a sheet that has several hundred rows. I would like to start at
row 1, select the range from 1 to 13, create a named range from it,
then move to row 2 and repeat, throughout the sheet, until there are
no more 13 row ranges possible. How on earth would I go about doing
this?

Automatically define series of named ranges
 

run the following macro

Sub nameRange()
Range("A1").Select
RowCount = Selection.End(xlDown).Row

For i = 1 To RowCount
Range(Cells(i, 1), Cells(i, 13)).Select
ActiveWorkbook.Names.Add Name:="nameRow" & i
RefersToR1C1:="=Sheet1!R" & i & "C1:R" & i & "C13"
Next
End Sub

this would give the name as naemRow1, nameRow2,...

- Manges

--
Message posted from http://www.ExcelForum.com

Automatically define series of named ranges
 

Sub Name13()
Dim r&
With Range([a1], [a65536].End(xlUp))
For r = 1 To .Rows.Count Step 13
.Cells(r, 1).Resize(13, 1).Name = "rng" & Format(r \ 13 + 1, "000")
Next
End With
End Sub

keepITcool


you may change the width of the range in the resize


< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Jon Tillman wrote:

I have a sheet that has several hundred rows. I would like to start at
row 1, select the range from 1 to 13, create a named range from it,
then move to row 2 and repeat, throughout the sheet, until there are
no more 13 row ranges possible. How on earth would I go about doing
this?

Automatically define series of named ranges
 

On Fri, 18 Jun 2004 02:33:08 -0700, keepITcool
wrote:

Sub Name13()
Dim r&
With Range([a1], [a65536].End(xlUp))
For r = 1 To .Rows.Count Step 13
.Cells(r, 1).Resize(13, 1).Name = "rng" & Format(r \ 13 + 1, "000")
Next
End With
End Sub


This is almost what I want, but it should create output like this:

rng001 : A1:A13
rng002 : A2:A14

I do that by changing Step 13 to Step 1, right?

Automatically define series of named ranges
 

ah!..

misread your question.

WHY set up the names anyway?

just manually enter a name like rng13
then use simple worksheetfunction offset(rng13,r,0)
or even
=offset($a$1,r,0,13,1)


that might make your pipe smoke smoother


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Jon Tillman wrote:

On Fri, 18 Jun 2004 02:33:08 -0700, keepITcool
wrote:

Sub Name13()
Dim r&
With Range([a1], [a65536].End(xlUp))
For r = 1 To .Rows.Count Step 13
.Cells(r, 1).Resize(13, 1).Name = "rng" & Format(r \ 13 + 1,
"000")
Next
End With
End Sub


This is almost what I want, but it should create output like this:

rng001 : A1:A13
rng002 : A2:A14

I do that by changing Step 13 to Step 1, right?

Automatically define series of named ranges
 

On Fri, 18 Jun 2004 09:59:05 -0700, keepITcool
wrote:

ah!..

misread your question.

WHY set up the names anyway?

just manually enter a name like rng13
then use simple worksheetfunction offset(rng13,r,0)
or even
=offset($a$1,r,0,13,1)

okay, maybe I should explain what I want to do instead of the silly
way I am trying to do it;

I have two columns, a & b

a is filled with daily sales data.

I want b to find the largest number in the previous 13 rows of a.

For instance:

A B
100
20
30
123
34
90
45
34
76
58
32
89
12
56 123

If it were a small amount of data, a named range would work, as I
could do =MAX(NamedRange,1) where NamedRange = A1:A13, but given that
I have several thousands rows and want to calculate the value in B for
each row, I thought it would be easiest to create a lot of named
ranges and apply what I know.

If there is a better way, I would love to know.

Automatically define series of named ranges
 

Nevermind, I found it. What I wanted was:

=LARGE(OFFSET(B14,-14,0,13,1),1)

thanks for prodding me in the right direction!