locked
How do I defined a Range Name for a varying-length range of cells RRS feed

  • Question

  • VBA for Excel:  I have to define a name for a varying-length column of cells.  The name is used later to define the range for a Data Validation List.  I just cannot figure out (and the Help Function isn't assisting) how to define the Name, say "DropOrdNum" using a variable as the address for the last cell in the range.  It is easy to define a name for "O13:O2000" - no problem, but then I have a whole lot of blanks in my data validation drop-down list.  So how do I define a name for "O13:LastCell", by making "LastCell" the Range Variable (am I correct in this) for the address of the last cell in the active range and then defining the name "DropOrdNum" as being the range "O13" to either "O(the row number of the last cell)" or "LastCell", being the address of the last cell in the active range, which varies by the hour?
    Saturday, February 18, 2012 10:39 AM

Answers