There's a limit on how long all the addresses can be to make up that string that
refers to the range name.
How about this.
Select about groups of 40 cells at a time. Give each selection a nice name
(InputRng1, InputRng2, ..., InputRng7)
Then the code would change to:
option explicit
sub ClearInputRange()
with worksheets("Info")
union(.range("inputrng1"),.range("inputrng2"), .range("Inputrng3"), _
.range("inputrng4"),.range("inputrng5"), .range("Inputrng6"), _
.range("inputrng7")).clearcontents
end with
end sub
If you use more/less names, you'll have to modify the code.
Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Then back to excel and show the Forms toolbar.
Drag a button from the Forms toolbar to a nice spot.
I like to put it in row 1 and then freeze row 1 so that it's always visible
(select A2 and then Window|freeze Panes).
Rightclick on the button and choose Assign macro
and assign ClearInputRange to the button.
Jonibenj wrote:
Hi Dave,
How many cells can be defined under one name? When I select all 257 of
the input cells and try to name them, a number of them are dropped off
when I hit the enter key. It seems as though there is a limit to the
number of cells that can be held under one name.
I am not familiar with macros at all. Where do I enter it? My sheet
name is "Info". Do I insert this into the macro where you have written
"sheet1"?
Jonathan
--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=470075
--
Dave Peterson