Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Named Ranges in Chart Series | Charts and Charting in Excel | |||
automatically import multiple named-ranges from Excel to Access | Excel Discussion (Misc queries) | |||
show/hide multiple series using named ranges | Charts and Charting in Excel | |||
Using a series of named ranges in SUMPRODUCT | Excel Worksheet Functions | |||
Automatically increase Named Ranges | Excel Discussion (Misc queries) |