How do I select the first cell of a filtered list?

[Shane Moore]

I have an autofilter on a column with 300+ rows.

If I filter on that column, how can I reference/extract the contents of
the very first row returned from the filter even when that first row may
have an 'actual' row number of 178 (or whatever)?

Thanks,

Shane.

 

[Dave Peterson]

In code???

One way:

Option Explicit
Sub testme()

Dim myCell As Range

Set myCell = Nothing
On Error Resume Next
With ActiveSheet.AutoFilter.Range
Set myCell = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Else
MsgBox myCell.Row
'mycell.entirerow.select '????
End If
End Sub

(No validation to make sure the worksheet is filtered.)

 

[Bernie Deitrick]

Shane,

You need to add another column to your table. Let's say that your table is in columns A to C, with
headers in row 1. Insert a new column A, so that your table is in columns B to D, and in cell A2,
enter the formula

=SUBTOTAL(3,$C$2:C2)

and copy down that down column A to match your table.

Then use the formula

=VLOOKUP(1,$A$1:$D$300,3,FALSE)

to return the first visible value from column C.

HTH,
Bernie
MS Excel MVP