Get value from first visible row after AutoFilter applied?

J

jg70124

I'm using the following VBA code (which I stole from this board, natch)
to dynamically create print headers and footers:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
With wsSheet
If .Name = "PriceSheets" Then _
.PageSetup.CenterHeader = "&""Arial,Bold""Prices"
.PageSetup.LeftHeader = Chr(10) & _
"&""Arial,Bold""Customer: " & .Range("c2").Text
& _
Chr(10) & "Location: " & .Range("d2").Text
.PageSetup.RightHeader = Chr(10) & _
"&""Arial,Bold""All Prices FOB " &
..Range("b2").Text & _
Chr(10) & "Effective Date: " &
..Range("f2").Text
End With
Next wsSheet
End Sub

Is there a way to replace the fixed cell references with references to
the cells which are visible only after the user has applied an
autofilter?

Thanks.

PS: I hope to be in Atlantic City for Deb's Excel Conference ...
 
Top