Count unique text values in excel range with blank cells

[nostradamus]

Hello, I have used this formula for counting unique text values, but would like to set the formula for a spreadsheet where I can copy and paste data to get the unique occurances.
But the problem I found is that I have to set the range manually each time for this formula to work properly.
For instance, here the cell range to be counted is from A2 to A26

=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A26,A2:A26,0),ROW(A2:A26)-ROW(A2)+1)>0))

I would like to set the range from cells A2 to A1000, when the actual data could only be for 100 cells or 500 cells depending on the dataset I use.
So, when I set the data range in the formula upto A1000, "N/A" is my result.

=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A1000,A2:A1000,0),ROW(A2:A22000)-ROW(A2)+1)>0))

Thanks in advance

 

[Peter_SSs]

Updated my profile as you suggested, thanks.

.. and thanks for attending to that too.