how do i create a named range excluding particular cells

[Guest]

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

 

[Guest]

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

 

[helhadad]

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

 

[adam_kroger]

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 (A48, A1115, ...). 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?

 

[RagDyeR]

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!
=====================================================

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 (A48, A1115, ...). 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?