Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
Hi,
This is a two part question that may have one answer. 1. I'm using the following code to create a named range of **everything** on a worksheet: Sub CreateRangeName() Dim first_row As Integer Dim first_col As Integer Dim num_rows As Integer Dim num_cols As Integer Dim rng As String With ActiveSheet first_row = .UsedRange.Row first_col = .UsedRange.Column num_rows = .UsedRange.Rows.Count num_cols = .UsedRange.Columns.Count End With rng = "R" & first_row & "C" & first_col & ":R" & num_rows & "C" & num_cols ActiveWorkbook.Names.Add Name:="RangeName", RefersToR1C1:="=" & rng End Sub This works fine on a worksheet where I want to select everything but there maybe occasion where I want to **leave out a section** from the range. Is there a better way I could code this so I can be more specific about the range I want to name but without hardcoding cell addresses. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
How about something like
Set rng = Range("A1:A10, B5:B8,H5:H18, M5:O10") rng.Name = "RangeName" -- HTH Bob Phillips "Don Guillett" wrote in message ... try ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection -- Don Guillett SalesAid Software "PC" <paulm dot c @ iol dot ie wrote in message ... Hi, This is a two part question that may have one answer. 1. I'm using the following code to create a named range of **everything** on a worksheet: Sub CreateRangeName() Dim first_row As Integer Dim first_col As Integer Dim num_rows As Integer Dim num_cols As Integer Dim rng As String With ActiveSheet first_row = .UsedRange.Row first_col = .UsedRange.Column num_rows = .UsedRange.Rows.Count num_cols = .UsedRange.Columns.Count End With rng = "R" & first_row & "C" & first_col & ":R" & num_rows & "C" & num_cols ActiveWorkbook.Names.Add Name:="RangeName", RefersToR1C1:="=" & rng End Sub This works fine on a worksheet where I want to select everything but there maybe occasion where I want to **leave out a section** from the range. Is there a better way I could code this so I can be more specific about the range I want to name but without hardcoding cell addresses. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
activesheet.usedrange.name = "MyName"
-- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bob Phillips wrote : How about something like Set rng = Range("A1:A10, B5:B8,H5:H18, M5:O10") rng.Name = "RangeName" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
OP wanted to leave out sections.
-- HTH Bob Phillips "keepITcool" wrote in message ft.com... activesheet.usedrange.name = "MyName" -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bob Phillips wrote : How about something like Set rng = Range("A1:A10, B5:B8,H5:H18, M5:O10") rng.Name = "RangeName" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
yep, missed that..:) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bob Phillips wrote : OP wanted to leave out sections. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
Hi Bob,
Thanks for the reply. The only problem here is the range is hardcode. I can afford to hardcode the starting cell of the range but after that the range might change daily. xlright and xlend won't work from the starting point (Say Cell A2) as these functions might not cover that required range - I'll layout an example A B C D 1 2 Blah1 Blah2 Blah3 3 1234 1234 1234 1234 4 1234 1234 1234 1234 5 1234 1234 This range could go beyound Row 5 and Col D do hard coding the range is not an option Thanks agin for your input Paul "Bob Phillips" wrote in message ... How about something like Set rng = Range("A1:A10, B5:B8,H5:H18, M5:O10") rng.Name = "RangeName" -- HTH Bob Phillips "Don Guillett" wrote in message ... try ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection -- Don Guillett SalesAid Software "PC" <paulm dot c @ iol dot ie wrote in message ... Hi, This is a two part question that may have one answer. 1. I'm using the following code to create a named range of **everything** on a worksheet: Sub CreateRangeName() Dim first_row As Integer Dim first_col As Integer Dim num_rows As Integer Dim num_cols As Integer Dim rng As String With ActiveSheet first_row = .UsedRange.Row first_col = .UsedRange.Column num_rows = .UsedRange.Rows.Count num_cols = .UsedRange.Columns.Count End With rng = "R" & first_row & "C" & first_col & ":R" & num_rows & "C" & num_cols ActiveWorkbook.Names.Add Name:="RangeName", RefersToR1C1:="=" & rng End Sub This works fine on a worksheet where I want to select everything but there maybe occasion where I want to **leave out a section** from the range. Is there a better way I could code this so I can be more specific about the range I want to name but without hardcoding cell addresses. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to to create named ranges
Hi Paul,
Are you wanting to reference A1:C5 in its totality, accepting that it may extend to say A1:H6, or are you wanting just to reference A2:C2, A3:D3, A4:D4, B5:C5? If it is the latter, can I ask why, as the others are I presume empty, so you can manage that in the code? -- HTH Bob Phillips "PC" <paulm dot c @ iol dot ie wrote in message ... Hi Bob, Thanks for the reply. The only problem here is the range is hardcode. I can afford to hardcode the starting cell of the range but after that the range might change daily. xlright and xlend won't work from the starting point (Say Cell A2) as these functions might not cover that required range - I'll layout an example A B C D 1 2 Blah1 Blah2 Blah3 3 1234 1234 1234 1234 4 1234 1234 1234 1234 5 1234 1234 This range could go beyound Row 5 and Col D do hard coding the range is not an option Thanks agin for your input Paul "Bob Phillips" wrote in message ... How about something like Set rng = Range("A1:A10, B5:B8,H5:H18, M5:O10") rng.Name = "RangeName" -- HTH Bob Phillips "Don Guillett" wrote in message ... try ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection -- Don Guillett SalesAid Software "PC" <paulm dot c @ iol dot ie wrote in message ... Hi, This is a two part question that may have one answer. 1. I'm using the following code to create a named range of **everything** on a worksheet: Sub CreateRangeName() Dim first_row As Integer Dim first_col As Integer Dim num_rows As Integer Dim num_cols As Integer Dim rng As String With ActiveSheet first_row = .UsedRange.Row first_col = .UsedRange.Column num_rows = .UsedRange.Rows.Count num_cols = .UsedRange.Columns.Count End With rng = "R" & first_row & "C" & first_col & ":R" & num_rows & "C" & num_cols ActiveWorkbook.Names.Add Name:="RangeName", RefersToR1C1:="=" & rng End Sub This works fine on a worksheet where I want to select everything but there maybe occasion where I want to **leave out a section** from the range. Is there a better way I could code this so I can be more specific about the range I want to name but without hardcoding cell addresses. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges in Excel (update and create) | Excel Discussion (Misc queries) | |||
Rapidly Create a Large Number of Named Ranges | Excel Worksheet Functions | |||
Create list of Named Ranges | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
named ranges - changing ranges with month selected | Excel Programming |