range name nonadjacent cells

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

I select 44 areas of nonadjacent cells on one worksheet
and give them one name.

Then I try to clearcontents this way:

Sub MakeDeletions()
[Period2].ClearContents
End Sub

Only 31 of the areas clear. 13 still have the data in
them.

I can't seem to find anything on this limitation so
perhaps it is my mistake? Assuming it was me, I kept
deleting the range name and trying to select it very
carefully again - several times. It lets me create the
name but when I attempt to clear it - that's where the
problem occurs.

Thank you-
 
Hi Sharon:

It appears that there is a limitation on how many discontinuous areas you
can include in a named range.

Regards,

Vasant.
 
Sharon,

Try this. Split your ranges into a number of named
ranges, and then combine those named ranges into
a "master" named range.

For example, this works (albeit with small ranges):

Range1 = Sheet1!$K$5,Sheet1!$E$5:$E$9,Sheet1!$H$7,Sheet1!
$G$11:$G$16
Range2 = Sheet2!$B$17:$B$22,Sheet2!$D$19,Sheet2!
$F$21:$I$22,Sheet2!$K$17

Then combined into:
MasterRng = Range1,Range2

In your macro:
Sub MakeDeletions()
[MasterRng].ClearContents
End Sub

Regards,

Mike
-----Original Message-----
Hi Sharon:

It appears that there is a limitation on how many discontinuous areas you
can include in a named range.

Regards,

Vasant.

Sharon said:
I select 44 areas of nonadjacent cells on one worksheet
and give them one name.

Then I try to clearcontents this way:

Sub MakeDeletions()
[Period2].ClearContents
End Sub

Only 31 of the areas clear. 13 still have the data in
them.

I can't seem to find anything on this limitation so
perhaps it is my mistake? Assuming it was me, I kept
deleting the range name and trying to select it very
carefully again - several times. It lets me create the
name but when I attempt to clear it - that's where the
problem occurs.

Thank you-
Click to expand...


.
Click to expand...
 
Oops - I am not getting the syntax right.
I tried this with and without the quotes (and with and
without brackets around "MasterRng"). It says there is a
compile error (syntax error) and indicates that it
expected a closing parenthesis after the first range
(Period1a).

MasterRng = ("Period1a", "Period1b")

I also tried declaring MasterRng as a range.

Thanks,
Sharon

-----Original Message-----
Sharon,

Try this. Split your ranges into a number of named
ranges, and then combine those named ranges into
a "master" named range.

For example, this works (albeit with small ranges):

Range1 = Sheet1!$K$5,Sheet1!$E$5:$E$9,Sheet1!$H$7,Sheet1!
$G$11:$G$16
Range2 = Sheet2!$B$17:$B$22,Sheet2!$D$19,Sheet2!
$F$21:$I$22,Sheet2!$K$17

Then combined into:
MasterRng = Range1,Range2

In your macro:
Sub MakeDeletions()
[MasterRng].ClearContents
End Sub

Regards,

Mike
-----Original Message-----
Hi Sharon:

It appears that there is a limitation on how many discontinuous areas you
can include in a named range.

Regards,

Vasant.

Sharon said:
I select 44 areas of nonadjacent cells on one worksheet
and give them one name.

Then I try to clearcontents this way:

Sub MakeDeletions()
[Period2].ClearContents
End Sub

Only 31 of the areas clear. 13 still have the data in
them.

I can't seem to find anything on this limitation so
perhaps it is my mistake? Assuming it was me, I kept
deleting the range name and trying to select it very
carefully again - several times. It lets me create the
name but when I attempt to clear it - that's where the
problem occurs.

Thank you-
Click to expand...


.
Click to expand...
.
Click to expand...
 
Insert =>Name => Define

Name: MasterRng
Refersto: =Period1a, Period1b

or in VBA
Union(Range("Period1a"),Range("Period1b)).Name = "MasterRng"

--
Regards,
Tom Ogilvy

Sharon said:
Oops - I am not getting the syntax right.
I tried this with and without the quotes (and with and
without brackets around "MasterRng"). It says there is a
compile error (syntax error) and indicates that it
expected a closing parenthesis after the first range
(Period1a).

MasterRng = ("Period1a", "Period1b")

I also tried declaring MasterRng as a range.

Thanks,
Sharon

-----Original Message-----
Sharon,

Try this. Split your ranges into a number of named
ranges, and then combine those named ranges into
a "master" named range.

For example, this works (albeit with small ranges):

Range1 = Sheet1!$K$5,Sheet1!$E$5:$E$9,Sheet1!$H$7,Sheet1!
$G$11:$G$16
Range2 = Sheet2!$B$17:$B$22,Sheet2!$D$19,Sheet2!
$F$21:$I$22,Sheet2!$K$17

Then combined into:
MasterRng = Range1,Range2

In your macro:
Sub MakeDeletions()
[MasterRng].ClearContents
End Sub

Regards,

Mike
-----Original Message-----
Hi Sharon:

It appears that there is a limitation on how many discontinuous areas you
can include in a named range.

Regards,

Vasant.

I select 44 areas of nonadjacent cells on one worksheet
and give them one name.

Then I try to clearcontents this way:

Sub MakeDeletions()
[Period2].ClearContents
End Sub

Only 31 of the areas clear. 13 still have the data in
them.

I can't seem to find anything on this limitation so
perhaps it is my mistake? Assuming it was me, I kept
deleting the range name and trying to select it very
carefully again - several times. It lets me create the
name but when I attempt to clear it - that's where the
problem occurs.

Thank you-


.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top