eg i want to create a range from A1 - A100 but i dont want to include A25 and
A48 can someone give me the code for this thankyou

Select the required cells, then Insert=Name=Define ..."MyRange"

You should get this:

refers to:

=Sheet1!$A$1:$A$24,Sheet1!$A$26:$A$47,Sheet1!$A$49 :$A$100

"RobG2007" wrote:

eg i want to create a range from A1 - A100 but i dont want to include A25 and
A48 can someone give me the code for this thankyou

On Jul 25, 11:08 am, RobG2007
wrote:
eg i want to create a range from A1 - A100 but i dont want to include A25 and
A48 can someone give me the code for this thankyou

Hi Rob,
You can divide the Range as you need as follows:

Sub CreateMyRange()
Dim MyRange As Range

Set MyRange = Application.Union(Range("A1:A24"), Range("A26:A47"),
Range("A49:A100"))
MyRange.Select ' Just to confirm the defined range. you can remove
this line later
MyRange.Name = "MyName"

End Sub

Hope this helps,

Regards,

Hesham ELhadad

Is there a limit as to how many ranges can be joined in this manner?
I was trying to name a range on a worksheet that was 52 non-connecting
fields of 20 cells (A4:D8, A11:D15, ...). However, when I would check
the range anterwords, the first 10 sections or so would not be
included in the Named Range. User error, or Excel limitation?


On Jul 25, 3:35 am, wrote:
On Jul 25, 11:08 am, RobG2007
wrote:

eg i want to create a range from A1 - A100 but i dont want to include A25 and
A48 can someone give me the code for this thankyou

Hi Rob,
You can divide the Range as you need as follows:

Sub CreateMyRange()
Dim MyRange As Range

Set MyRange = Application.Union(Range("A1:A24"), Range("A26:A47"),
Range("A49:A100"))
MyRange.Select ' Just to confirm the defined range. you can remove
this line later
MyRange.Name = "MyName"

End Sub

Hope this helps,

Regards,

Hesham ELhadad

Here's a link to an old thread that addresses this subject.

http://tinyurl.com/2exkca

There are 2 solutions suggested ... one by Dave Peterson using VBA and
another where I use a combination of smaller ranges to make one large, all
encompassing range.

Your choice since both work.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

wrote in message
ups.com...
Is there a limit as to how many ranges can be joined in this manner?
I was trying to name a range on a worksheet that was 52 non-connecting
fields of 20 cells (A4:D8, A11:D15, ...). However, when I would check
the range anterwords, the first 10 sections or so would not be
included in the Named Range. User error, or Excel limitation?


On Jul 25, 3:35 am, wrote:
On Jul 25, 11:08 am, RobG2007
wrote:

eg i want to create a range from A1 - A100 but i dont want to include
A25 and
A48 can someone give me the code for this thankyou

Hi Rob,
You can divide the Range as you need as follows:

Sub CreateMyRange()
Dim MyRange As Range

Set MyRange = Application.Union(Range("A1:A24"), Range("A26:A47"),
Range("A49:A100"))
MyRange.Select ' Just to confirm the defined range. you can remove
this line later
MyRange.Name = "MyName"

End Sub

Hope this helps,

Regards,

Hesham ELhadad