Macro to define the Range

mubi_masti

New member
Joined
Oct 8, 2011
Messages
14
Reaction score
0
Points
0
One of the member of this fourm has provided an excel sheet in which by clicking the button whole date in the sheet selected. Now

i want to develop a macro which define the name of that range and copy the range reference in B2 cell


for example , name range as "tblheadings" and copy reference in B2 as: Sheet!$A$1:$A$5
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Not sure what range you want to assign the name to? And what happens if it is a different size to Sheet!$A$1:$A$?
 

mubi_masti

New member
Joined
Oct 8, 2011
Messages
14
Reaction score
0
Points
0
dear bob

whin the help of specialcell, the button select the cells that have formulae, if i decrease or increase the cell containing formulae the range of selected cells varies.

I want to give one fixed name ("tblheading".......... let say) what ever the range of cells.

we want following outcome

name: tblheading
b2 = Sheet!$A$1:$A$5

if i change the selected range then
name: tblheading
b2 = Sheet!$A$1:$b$5

i think it will explain what i want to do...........
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Out of curiosity, what version of Excel are you using? If you're using 2007 or 2010, you can format your table as an Excel table, then set the name on the Table Tools ->Design->Table Name. At that point it will always update.

If you're using Excel 2003 or ealier, you'd need to do some trickery to accomplish this..
 
Top