Dynamic Name Ranges with blank cells amongst the data

Hello,

I import data from web and it it comes in multiple pages, it creates an empty row between pages in excel.

I need to name each column where the number of rows varies and there are blanks in between them.

So far I have tried:

Please Login or Register  to view this content.

in the name manager, but this counts ALL blank cell which slows the sheet and creates problems for sumproduct formula (gives a REF error).

Also, i'm trying to avoid VBA for this. So, is there a way to point to the last cell (row) in a column?

Thanks in advance!

Attach a sample workbook showing the data you import from the web.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

Book3.xlsx

for example, the attached pulls data from yahoo

please check the formula for the name ranged

a friend suggested the (match(rept workaround which doesn't work for numbers

again, i need to have the named ranges for a dynamic data set for sumproduct formula which required ranges to have the same number of rows, hopefully without a vba if possible

(i have used below for the vba in the past)

Please Login or Register  to view this content.