Results 1 to 4 of 4

Thread: Macro to define the Range

  1. #1

    Macro to define the Range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    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$?

  3. #3
    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...........

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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..
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •