Ignore blank cells in Excel Named Range

Question

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?

Answer 1

You can make the range name dynamic by using a formula instead of a direct cell reference.

If you want the range name to start in A2 and extend to the last row of data in column A, then you can use this formula

If column A has text, use

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH("zzzzzzz",Sheet1!$A:$A,1))

If column A has numbers, use

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(99^99,Sheet1!$A:$A,1))

The Match() function in these formulas will return the row number of the last row with data, and the Index function will return the cell reference for that row in column A.

Answer 2

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..

Answer 3

I have tried the method above and each part works fine on a sheet and returns the cell contents in a range when pasted into the sheet together. However, when I try to apply it in data validation as a range it throws a formula error, nothing specific just that it looks like you are trying to enter a formula one.

=INDEX(tDPO[#Data],1,MATCH("ListName",tDPO[#Headers],0)):INDEX(tDPO[ListName],MATCH("",tDPO[ListName],-1))