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

Using VB to to create named ranges
 

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

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

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"

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"

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.

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

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