View Single Post
  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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