Bob,

Thanks for taking the time to work on my problem. I created the suggested
macro and reffered to it in my filtering macro but it didn't seem to do
anything different.
Another poster has replied with an alternative solution that extracts the
correct info but it is in amessage box (that was my fault not saying I need
the info in a particular cell (E2).
I really appreciate you having taken the time to reply.
sincerely

Jeff

"Bob Phillips" wrote in message
...
Untested, but maybe something like this using VBA

iLastRow = Cells(Rows.Count,"B").End(xlUp).Row
Set myRange = Range("B9:B" & iLastRow).SpecialCells(xlCellTypeVisible)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" wrote in message
...
I have a filtered range. I am trying to extract the value of the
contents
of the cell in column B which is always immediately below cell B8. (Rows
1
through 8 are reserved for display data summary and KPI's and the window
is
split and frozen Row1:Row8).
Depending on the filter criteria used this row number / the cell "B9" I
am
trying to identify may be anything from 9 to 1200.

All my attempts to "capture" the contents of the "effective cell B9"
returns
either the value of the first record (when using range name) or the cell
value in the present filtered range being examined. The latter will vary
depending on the filter criteria used.

In effect, my question is, is there any way, the contents of cell Bxxx
can
be extracted as if it were effectively B9?