Creating Dynamic Ranges in Excel With a Macro

Roger Govier has taken the pain out of creating dynamic ranges, by creating a macro to automatically create the ranges for you.

When you run the macro in Excel, it creates a series of names for each of the column headings on the sheet. In addition to the Names for the column ranges, 3 extra names are defined:

  •   lrow for the last row number used on the sheet
  •   lcol for the last column number used on the sheet
  •   myData for the complete range of Data including the Header row.

Roger uses the INDEX function in the names, instead of OFFSET, which is volatile, and could slow down your workbook.


To see the code, and read a description of Roger's technique, you can visit the Create Dynamic Ranges With a Macro page.

You can download the zipped sample file there, to see how Roger's technique works.


You may also like...

4 Responses

  1. sam says:

    The Problem with Counta(A:A) is It works provided you dont have blanks

    Find the LastRow using Match(“*”,A:A,-1) if Column A contains Text Data with Blanks
    If Col A contains Numbers with blanks use Match(9.9E+307,A:A)
    If it contains Mixed Data type use

  2. Roger Govier says:

    Hi Sam

    Thank you for those very valid comments, and there is no reason why you cannot substitute any of those formulae in place of the CountA() functions.

    Your Mixed data one will fail however if the column can contain mixed data but only happens to contain Text OR Numbers.
    It really needs to be

    However, if one is dealing with a data table, there will normally be one column that has values in every row, and that is generally the first column where there might be names or dates etc. If this is not true, then I wouldn’t really regard the data as being a table that one would want to be analysing with Sumproduct or Pivot Tables.

    Lrow is based upon that one column (where I would normally not expect to find gaps.). and that is used for determining the length of all subsequent ranges, so they are all of equal dimension – absolutely vital for Sumproduct formulae to work.

    So, I am not worried at all about gaps in data for any other column, as it is of no significance in determining the dynamic range.

    I have a Constant called Offset. I can see that it would be sensible to Name that as ROffset have another called COffset, so one could use a column other than the first as the column to Count.
    In that case teh creation of “lrow” would be
    wb.Names.Add Name:=”lrow”, RefersToR1C1:=”=COUNTA(C” & Colno + COffset & “)”

    and the creation of the individual name would be

    wb.Names.Add Name:=myName, RefersToR1C1:= _
    “=R” & Rowno + ROffset & “C” & i & “:INDEX(C” & i & “,lrow)”

  3. Matt says:

    The problem with using xlToRight to find the last column containing a header is that any headings that follow a blank cell are not included. The error handling for blank header names is never triggered as lcol detects the last non-blank cell in the header row.

    It may be better to get the last column to check( and/or row) using SpecialCells(xlLastCell) or UsedRange.Columns.Count

    But oh how I wish I had got around to doing this myself a few years ago. Thx

  4. Roger Govier says:

    Hi Matt

    You are absolutely right. I had put in the lcol to try and pick up columns without a heading, but as you rightly say, the check never gets triggered.

    I think I will change the line in the code to

    lcol = Cells(Rowno, Columns.Count).End(xlToLeft).Column

    Using Columns.Count will then deal with Xl2007 as well as earlier versions

Leave a Reply

Your email address will not be published. Required fields are marked *