What previous post are you referring to?

If you have empty cells *within* the range then you have to identify the
last filled cell:

http://xldynamic.com/source/xld.LastValue.html

Biff

Hi

It depends on your data. You need some formula, which locates the last row
of used range. Some possibilities:

a) You use a helping column as key. The column contains a formula, which
counts rows in used range. P.e. column B contains some data, with header in
B1. Into A2 you enter the formula
=IF(OR(B2<>"",A3<>""),ROW()-1,"")
, and copy it down. Now you can define a named range for column B as
=OFFSET(Sheet1!$B$1,1,,MAX(Sheet1!$A:$A),1)

b) You create an UDF, which returns the number of rows/columns in used
range, and then use this UDF to define a named range.

c) You use some built-in formula to calculate the last row in used range.
P.e. when your data in column A are sorted ascending
=OFFSET(Sheet1!$A$1,1,,CHOOSE(MAX(Sheet1!$A$2:$A$1000),Sheet1!$A$2:$A$1000,0
),1)

Arvi Laanemets

where you are looking for a larger than possible number or letter.
=OFFSET(Sheet1!$A$1,0,0,MAX(MATCH(99999,Sheet1!$A:$A),MATCH("zzzz",Sheet1!$A:$A)),1)