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
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    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..
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- 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