Roger Govier has taken the pain out of creating dynamic ranges, by writing an Excel macro to automatically create the ranges for you.
What Macro Does
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.
INDEX Function
Roger uses the INDEX function in the names, instead of OFFSET, which is volatile, and could slow down your workbook.
Get the Dynamic Range Workbook
To see the code, and read a detailed description of Roger’s technique, you can visit the Create Dynamic Ranges With a Macro page on my Contextures site.
You can download the zipped sample file there, to see how Roger’s technique works.
___________________
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
Max(Match(“*”,A:A,-1),Match(9.9E+307,A:A))
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
=MAX(IF(ISERROR(MATCH(“*”,A:A,-1)),0,MATCH(“*”,A:A,-1)),
IF(ISERROR(MATCH(9.9E+307,A:A)),0,MATCH(9.9E+307,A:A)))
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)”
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
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