How can I change this:

=COUNTIF($B$7:$B$2000,Z8)

to something which will 'detect' the max row numberin column 'B'?
2000 is an arbitrary figure and will fluctuate either way so I was looking
for a 'catch all'

Thanks,

Traa Dy Liooar

Jock

Jock

If the data is contiguous then

=COUNTA(B:B)

will give you the number of rows in ColumnB with data, if there are gaps, it
will still return a number, but will not count the blanks

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"Jock" wrote in message
...
How can I change this:

=COUNTIF($B$7:$B$2000,Z8)

to something which will 'detect' the max row numberin column 'B'?
2000 is an arbitrary figure and will fluctuate either way so I was looking
for a 'catch all'

Thanks,

Traa Dy Liooar

Jock

why bother?

=COUNTIF(B7:B65536,Z8)

covers all cases
--
Gary''s Student - gsnu200752


"Jock" wrote:

How can I change this:

=COUNTIF($B$7:$B$2000,Z8)

to something which will 'detect' the max row numberin column 'B'?
2000 is an arbitrary figure and will fluctuate either way so I was looking
for a 'catch all'

Thanks,

Traa Dy Liooar

Jock

one option is to use a dynamic range. check debra's site

http://www.contextures.com/xlNames01.html

--


Gary


"Jock" wrote in message
...
How can I change this:

=COUNTIF($B$7:$B$2000,Z8)

to something which will 'detect' the max row numberin column 'B'?
2000 is an arbitrary figure and will fluctuate either way so I was looking
for a 'catch all'

Thanks,

Traa Dy Liooar

Jock

If there are no empty cells in your list this will report out the
exact sheet row number:

Dim nRow As Integer
Range("B7").Select
Selection.End(xlDown).Select
nRow = Selection.Row
MsgBox nRow


On Oct 29, 10:04 am, Jock wrote:
How can I change this:

=COUNTIF($B$7:$B$2000,Z8)

to something which will 'detect' the max row numberin column 'B'?
2000 is an arbitrary figure and will fluctuate either way so I was looking
for a 'catch all'

Thanks,

Traa Dy Liooar

Jock