Identify the contents of column in a filtered range

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

Is column B the second column of the filtered range? (Did you include column A
in your filtered range?)

If yes:

Option Explicit
Sub testme()

Dim rngF As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'header row only
MsgBox "No details shown. Please try again"
Exit Sub
End If
Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value

End With

End Sub



Jeff wrote:
>
> 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?

--

Dave Peterson

Dave,

Thanks for this. It works fine and yields the correct value in the message
box.
I would like this value to automatically display as a value in cell E2 so it
can be used in a formula (dependant on the value in E2).

Is it possible? Is this a big ask?

I ereally appreciate you taking the time to solve this problem - I have
spent hours trying all the obvious techniques (and I learn / upskill
everytime I post).

sincerely
Jeff


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Is column B the second column of the filtered range? (Did you include
> column A
> in your filtered range?)
>
> If yes:
>
> Option Explicit
> Sub testme()
>
> Dim rngF As Range
>
> With ActiveSheet.AutoFilter.Range
> If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
> 'header row only
> MsgBox "No details shown. Please try again"
> Exit Sub
> End If
> Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
> .Cells.SpecialCells(xlCellTypeVisible)
>
> MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value
>
> End With
>
> End Sub
>
>
>
> Jeff wrote:
>>
>> 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?
>
> --
>
> Dave Peterson

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>
>

Option Explicit

Sub testme()
Dim rngF As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'header row only
MsgBox "No details shown. Please try again"
Exit Sub
End If
Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

Range("E2").Value = rngF.Cells(1).Address & vbLf &
rngF.Cells(1).Value

End With

End Sub



--

HTH

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


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Dave,
>
> Thanks for this. It works fine and yields the correct value in the
message
> box.
> I would like this value to automatically display as a value in cell E2 so
it
> can be used in a formula (dependant on the value in E2).
>
> Is it possible? Is this a big ask?
>
> I ereally appreciate you taking the time to solve this problem - I have
> spent hours trying all the obvious techniques (and I learn / upskill
> everytime I post).
>
> sincerely
> Jeff
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Is column B the second column of the filtered range? (Did you include
> > column A
> > in your filtered range?)
> >
> > If yes:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim rngF As Range
> >
> > With ActiveSheet.AutoFilter.Range
> > If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1
Then
> > 'header row only
> > MsgBox "No details shown. Please try again"
> > Exit Sub
> > End If
> > Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
> > .Cells.SpecialCells(xlCellTypeVisible)
> >
> > MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value
> >
> > End With
> >
> > End Sub
> >
> >
> >
> > Jeff wrote:
> >>
> >> 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?
> >
> > --
> >
> > Dave Peterson
>
>

I have figured out how to use the suggested code to do the job I envisioned.

Many thanks.

"Jeff" <[email protected]> wrote in message
news:[email protected]...
>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?
>

I have figured out how to use the suggested code to do the job I envisioned.

Many thanks.

"Jeff" <[email protected]> wrote in message
news:[email protected]...
>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?
>

Option Explicit

Sub testme()
Dim rngF As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'header row only
MsgBox "No details shown. Please try again"
Exit Sub
End If
Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

Range("E2").Value = rngF.Cells(1).Address & vbLf &
rngF.Cells(1).Value

End With

End Sub



--

HTH

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


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Dave,
>
> Thanks for this. It works fine and yields the correct value in the
message
> box.
> I would like this value to automatically display as a value in cell E2 so
it
> can be used in a formula (dependant on the value in E2).
>
> Is it possible? Is this a big ask?
>
> I ereally appreciate you taking the time to solve this problem - I have
> spent hours trying all the obvious techniques (and I learn / upskill
> everytime I post).
>
> sincerely
> Jeff
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Is column B the second column of the filtered range? (Did you include
> > column A
> > in your filtered range?)
> >
> > If yes:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim rngF As Range
> >
> > With ActiveSheet.AutoFilter.Range
> > If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1
Then
> > 'header row only
> > MsgBox "No details shown. Please try again"
> > Exit Sub
> > End If
> > Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
> > .Cells.SpecialCells(xlCellTypeVisible)
> >
> > MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value
> >
> > End With
> >
> > End Sub
> >
> >
> >
> > Jeff wrote:
> >>
> >> 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?
> >
> > --
> >
> > Dave Peterson
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>
>

Dave,

Thanks for this. It works fine and yields the correct value in the message
box.
I would like this value to automatically display as a value in cell E2 so it
can be used in a formula (dependant on the value in E2).

Is it possible? Is this a big ask?

I ereally appreciate you taking the time to solve this problem - I have
spent hours trying all the obvious techniques (and I learn / upskill
everytime I post).

sincerely
Jeff


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Is column B the second column of the filtered range? (Did you include
> column A
> in your filtered range?)
>
> If yes:
>
> Option Explicit
> Sub testme()
>
> Dim rngF As Range
>
> With ActiveSheet.AutoFilter.Range
> If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
> 'header row only
> MsgBox "No details shown. Please try again"
> Exit Sub
> End If
> Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
> .Cells.SpecialCells(xlCellTypeVisible)
>
> MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value
>
> End With
>
> End Sub
>
>
>
> Jeff wrote:
>>
>> 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?
>
> --
>
> Dave Peterson

Is column B the second column of the filtered range? (Did you include column A
in your filtered range?)

If yes:

Option Explicit
Sub testme()

Dim rngF As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'header row only
MsgBox "No details shown. Please try again"
Exit Sub
End If
Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value

End With

End Sub



Jeff wrote:
>
> 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?

--

Dave Peterson

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

I have figured out how to use the suggested code to do the job I envisioned.

Many thanks.

"Jeff" <[email protected]> wrote in message
news:[email protected]...
>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?
>

Option Explicit

Sub testme()
Dim rngF As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'header row only
MsgBox "No details shown. Please try again"
Exit Sub
End If
Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

Range("E2").Value = rngF.Cells(1).Address & vbLf &
rngF.Cells(1).Value

End With

End Sub



--

HTH

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


"Jeff" <[email protected]> wrote in message
news:[email protected]...
> Dave,
>
> Thanks for this. It works fine and yields the correct value in the
message
> box.
> I would like this value to automatically display as a value in cell E2 so
it
> can be used in a formula (dependant on the value in E2).
>
> Is it possible? Is this a big ask?
>
> I ereally appreciate you taking the time to solve this problem - I have
> spent hours trying all the obvious techniques (and I learn / upskill
> everytime I post).
>
> sincerely
> Jeff
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Is column B the second column of the filtered range? (Did you include
> > column A
> > in your filtered range?)
> >
> > If yes:
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim rngF As Range
> >
> > With ActiveSheet.AutoFilter.Range
> > If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1
Then
> > 'header row only
> > MsgBox "No details shown. Please try again"
> > Exit Sub
> > End If
> > Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
> > .Cells.SpecialCells(xlCellTypeVisible)
> >
> > MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value
> >
> > End With
> >
> > End Sub
> >
> >
> >
> > Jeff wrote:
> >>
> >> 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?
> >
> > --
> >
> > Dave Peterson
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>
>

Dave,

Thanks for this. It works fine and yields the correct value in the message
box.
I would like this value to automatically display as a value in cell E2 so it
can be used in a formula (dependant on the value in E2).

Is it possible? Is this a big ask?

I ereally appreciate you taking the time to solve this problem - I have
spent hours trying all the obvious techniques (and I learn / upskill
everytime I post).

sincerely
Jeff


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Is column B the second column of the filtered range? (Did you include
> column A
> in your filtered range?)
>
> If yes:
>
> Option Explicit
> Sub testme()
>
> Dim rngF As Range
>
> With ActiveSheet.AutoFilter.Range
> If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
> 'header row only
> MsgBox "No details shown. Please try again"
> Exit Sub
> End If
> Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
> .Cells.SpecialCells(xlCellTypeVisible)
>
> MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value
>
> End With
>
> End Sub
>
>
>
> Jeff wrote:
>>
>> 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?
>
> --
>
> Dave Peterson

Is column B the second column of the filtered range? (Did you include column A
in your filtered range?)

If yes:

Option Explicit
Sub testme()

Dim rngF As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'header row only
MsgBox "No details shown. Please try again"
Exit Sub
End If
Set rngF = .Columns(2).Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

MsgBox rngF.Cells(1).Address & vbLf & rngF.Cells(1).Value

End With

End Sub



Jeff wrote:
>
> 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?

--

Dave Peterson

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

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?