Creating Dynamic Named Ranges in VBA II

I recently posted about Creating Dynamic Names in VBA where created a bunch of names that would expand with the data provided. I created names that look like

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$A:$A))

I like Tushar’s idea so much that I deleted all my names and redid them using Offset (except column A of course). Tushar commented that I should set up column A like I posted, but then use Offset for all the other columns. “Brilliant”, I thought. If I ever need to change how ranges are defined, I only have to change one name. A few days later, the end user decided they didn’t need one of the columns and deleted it. The problem was that it didn’t return an error, it just returned the wrong results. The offset numbers are constant so deleting a column caused all of the later names to be off by one.

I “solved” it by restoring the column and hiding it.

9 Comments

  1. Hui... says:

    I use this idea all the time and try and choose a field the user will never want to delete, generally Date.
    The good thing about this is that offset can use both Positive and Negative offsets and so it doesn’t matter where the column is.

  2. Jon Peltier says:

    You can also base the column offset on a match of column headers:

    =offset(DateRange,0,column(Match(“New Range”,$A:A$,0))-column(DateRange))

  3. Martin says:

    aahh, users…..

    there is some fault to blame on us, however,learned trough the years and spreadsheets of experience: it’s good to give them SOME -and I repeat – SOME info on how our sheets and/or macros work. And always protect the data tables.

    They always will forget what we have told them, and eventually we’ll just have to go back and undo if possible, that’s another fact.

    Jon, your idea is great, I’ll give it a try and see how it works. I always tend to define the dynamic ranges as offset of the first one, and I think it may help me with that kind of user behavior…

    Rgds.

  4. Doug Glancy says:

    To expand on Jon’s, and thwart those dang users should they change the column header text, you could give each column header a name and match that:

    =OFFSET(DateRange,0,COLUMN(NewRangeHeader)-COLUMN(DateRange))

    These are good discussions. They’ve got me to finally start storing these types of things in an organized way for future projects.

  5. Yard says:

    I use the MATCH technique to allow tables to expand, but not sure of the advantage of naming each column header. I just use:

    =OFFSET(AnchorRange,,COLUMN(MATCH($E$1,$1:$1,0))-COLUMN(AnchorRange))

    Of course, this can still be screwed by a user deleting column E, for example. Specify the column header and the user can edit the text; specify the column header address and the user can delete the column.

    The original point about creating names programmatically can probably provide the most secure method, I guess??

  6. hans schraven says:

    To introduce a more end-user-independent approach:

    Make a UDF

    Function A_rijen()
      A_rijen = ActiveSheet.UsedRange.Rows.Count
    End Function

    Use this UDF in the definition of dynamic named ranges:

    =OFFSET(Sheet1!$A$1;;;A_rijen())

    So whatever the end-user deletes, the usedrange will survive.

    PS.
    To your own liking you can use other UDF’s as well, for instance:

    Function A_rijen()
      A_rijen = ActiveSheet.cells(1,1).currentregion.Rows.Count
    End Function

    or

    Function A_rijen()
      A_rijen = ActiveSheet.UsedRange.columns(1).specialcells(2).Count
    End Function

    etc.

  7. fzz says:

    OFFSET still has the problem of volatility. My own rule-of-thumb is that if there are fewer than 1,000 cell formulas calling volatile functions, they those functions are more of a benefit than a headache. OTOH, I use a large model (written by someone else) which has 300K cell formulas calling volatile functions, and the recalc lag on even new machines is noticeable and irritating.

    It’s still possible to use INDEX, but it may seem contrived. With the worksheet-level name WSC defined as $1:$65536 and the base range named BASE, then define dynamic names as

    =INDEX(WSC,MIN(ROW(BASE)),MIN(COLUMN(BASE))+n):INDEX(WSC,MAX(ROW(BASE)),MIN(COLUMN(BASE))+n)

    where n would be an offset from BASE’s first column. Alternatively, define BASEROWS as

    =INDEX(WSC,MIN(ROW(BASE)),1):INDEX(WSC,MAX(ROW(BASE)),256)

    and define other names as

    =INDEX(BASEROWS,0,n)

    where n is the absolute column number. Or define BASEBLOCK as

    =INDEX(BASE,1,1):INDEX(WSC,MAX(ROW(BASE)),256)

    and define other names as

    =INDEX(BASEBLOCK,0,n)

    where n is the column number relative to BASE’s first column. Finally, I’d define BASE using

    MAX(MATCH({9E307;”zzzzzzzzzzzzzzzz”},range))

    rather than COUNTA(range) in order to avoid problems due to blank cells between nonblank cells. If users will delete entire columns, why believe they won’t clear arbitrary cells?

  8. sam says:

    @fzz
    BASE=MAX(MATCH({9E+307;”"},range,{1;-1})) is better

  9. Kedar says:

    I usually give =Column() command in row 1. (Most of the time I use first column and first row for my own CONSTANTS). I refer the offset command in named ranges with the row 1 cell so that if the user cuts a column and inserts into another location (entirely) then still my named ranges work.
    (Dont know if this makes my workbook a bit slow due to volatile functions, however this saves a lot of efforts for me.)

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: