try this where 99999 is any number larger than possible or use "zzzz" if
looking for text

=OFFSET(Inventory!$KB$2,0,0,SUMPRODUCT(--(Inventory!$KB$2:$KB$200<"")),1)
=OFFSET(Inventory!$KB$2,0,0,match(999999,inventory !$Kb:$Kb),1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dhstein" wrote in message
...

I have a Defined Name of "VendorList:

=OFFSET(Inventory!$KB$2,0,0,SUMPRODUCT(--(Inventory!$KB$2:$KB$200<"")),1)

I want this to be a dynamic range and to go from KB2 to the last cell with
data (KB144 at the moment). But there is a blank value in cell KB2 so the
range only goes to KB143. Any suggestions as to how to fix this? Thanks.