Q:

Ignore blank cells in Excel Named Range

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.
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.
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).
Or is there a better/easier way for me to achieve this functionality?

A:

Random Info About Why I'm Posting
First, I've used teylyn's recommended method (thanks for that!) which works fine, and adapted it to include any length of text, rather than limited to how many "z" you put in. 

Second, I've expanded the code for when people (like my use case) need/like to work with named tables, and headers.

Hope this helps.

Ignoring Blanks in a Named Range - Version 1
Using the same method as teylyn, instead of:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzzzz",Sheet1!$A:$A,1))

Try using "" as the minimum statement for text, so anything put into the text box would count as greater than that value, telling the Match function to count it:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("",Sheet1!$A:$A,-1))

The reason this should work is MATCH will take the last time it sees this value not to be true in that column (so antyhing that isn't a blank) as the row number it will count.  NOTE: This method does not work for numbers.

BEWARE: If there is a cell with a blank value before the last cell with a value in that column, the random blank will still be shown in the list! 

To remove this issue where random blank cells might still be shown, follow my next method:

Ignoring Blanks in a Named Range - Version 2
In order to filter a column of values so that all text is shown at the top of the columns, you can make the list a Table, give it a name (such as "Table_Name"), and give the column of your list a header (such as "Column_Header"). 
 If you want the Named Range to include your column header, and ignore all the blanks, try using:
=INDEX(Table_Name[[#Headers],[Column_Header],1):INDEX(Table_Name[Column_Header],MATCH("",Table_Name[Column_Header],-1))

If you'd prefer it to start with the first row of the Column's data, try using:

=INDEX(Table_Name[[#Data],[Column_Header],1):INDEX(Table_Name[Column_Header],MATCH("",Table_Name[Column_Header],-1))

I hope this makes sense.. I'm just defining the first row of each option in the table as the starting point for the Name Range. 
NOTE: Numbers won't work with these specific versions of MATCH, so don't forget to use the following version for MATCH:
MATCH(99^99,Table_Name[Column_Header],1)

Let me know if I'm missing anything! 
Side Note:
There's one more method I've used by another fellow StackOverflow Excel specialist, which I will try and find and link to this comment, for those of you interested in having an auto-updating list of values, based on a user's own input of values..

