Ignore blank cells in Excel Named Range
<p>I have a macro that copies data from a series of sheets into a master so that I can analyze the data in one place. The imported data exists in well defined columns so that I can use sumproduct formulas to generate reports.</p>

<p>I have used named ranges to give names to each column of data so I don't have to use the Sheets!A1 references in each of the sum product. But as the data will change size depending on what is imported I have the named range to be the entire size of the column A2:A1048576. This means it takes forever for the sumproduct to run.</p>

<p>Is there a way to limit the named range to only those cells that have data in them? They will always be the top n rows (depending on how much data is imported on each run).</p>

<p>Or is there a better/easier way for me to achieve this functionality?</p>