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.