Named range for next blank row

[theta]

Hi

Is there any way for a dynamic named range to be the range in A:F where no data follows (i.e. the next availabe row) - do not want it to be the next blank row as there are blanks in my data. So needs to the last row (range A:F) where all cells below (A:F to the end of the worksheet) are blank

Any ideas?

 

[Andrew Poulsom]

What sort of data do you have in column A - numbers or text?

 

[schielrn]

This is one way, but could probably be more efficient if we knew if there were going to be numbers or text in the cells:

Excel Workbook
	A	B	C	D	E	F	G	H
1	sadf							A30:F30
2	sadf	df
3		s	df
4				s
5				df
6			df	s
7		f	sd
8	df	s
9	sadf	df
10		s	df
11			f	s
12				df	f
13					s	d
14					df	fs
15				df	s
16			df	s
17		sdf	f
18
19		df	sdfs
20
21	sd
22
23	f		f
24		ds
25
26
27			6
28
29		w
30
31
32
33
34
35
Sheet14

#VALUE!

Hope that helps.

 

[theta]

Comlpete mixture of data. Can be any character. Anything from a space or a full stop..through to an alpha string

(dealing with random system reports that some people "fudge"...so looking for the next clean range to start working with)

 

[Andrew Poulsom]

Try defining the name as:

=INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A)+1):INDEX(Sheet1!$F:$F,MATCH(REPT("z",255),Sheet1!$A:$A)+1)

 

[theta]

Wont that just find the next blank in A and F. Or will it analyse B, C, D, E ?

 

[Andrew Poulsom]

It will find the next blank cell in column A and expand the range to include columns A:F.

 

[theta]

Hmmm...need to select the next A:F where all A:F cells after it are blank to the base of the worksheet...

So need to analyze A, B, C, D, F...

?

 

[Andrew Poulsom]

Try:

=INDEX(Sheet1!$A:$F,MAX((Sheet1!$A$1:$F$65535<>"")*(ROW(Sheet1!$A$1:$F$65535)))+1,0)

 

[theta]

Very nice! Very elegant and works a treat