Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name nonadjacent cells
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name nonadjacent cells
Hi Sharon:
It appears that there is a limitation on how many discontinuous areas you can include in a named range. Regards, Vasant. "Sharon" wrote in message ... 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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name nonadjacent cells
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" wrote in message ... 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- . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name nonadjacent cells
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" wrote in message .. . 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- . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range name nonadjacent cells
Insert =Name = Define
Name: MasterRng Refersto: =Period1a, Period1b or in VBA Union(Range("Period1a"),Range("Period1b)).Name = "MasterRng" -- Regards, Tom Ogilvy "Sharon" wrote in message ... 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" wrote in message .. . 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- . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I fill nonadjacent cells? | Excel Worksheet Functions | |||
Nonadjacent cells | Excel Discussion (Misc queries) | |||
How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
keyboard command used to select a range of nonadjacent cells? | Excel Discussion (Misc queries) | |||
keyboard command used to select a range of nonadjacent cells? | Excel Discussion (Misc queries) |