how to know rowindex after filtering

M

Maileen

Hi,

Via VBA i filtered a sheet. Basically i should have as result only 1 row.

How can i know what is the rowindex (figure in the grey column margin
e.g : 873, or 34245,...) ?

thanks a lot,
Maileen
 
D

Dave Peterson

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
End If
End Sub

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

CaptainQuattro

If you use one column of your data to contain the row number of each
row, you can use the Subtotal function to identify which row is
visible.

Assuming row 1 is always visible and contains your column headings,
and
assuming column A contains your row numbers.

Use the formula =Subtotal(9,$A$2:$A$60000). This returns the sum of
the visible
cells in column A.

Since only one row is visible at a time, the sum of the visible cells
will
be your row number.

You can also use Subtotal to identify up to two additional visible
rows.

=Subtotal(4,$A$2:$A$60000) will tell you the maximum visible value in
column A, i.e. the last visible row number.


=Subtotal(5,$A$2:$A$60000) will tell you the minimum visible value in
column A, i.e. the top visible row number.

If there are three visible rows, =Subtotal(9,$A$2:$A$60000) -
Subtotal(4,$A$2:$A$60000) - Subtotal(5,$A$2:$A$60000) will tell you the
row number of the second of the three rows.
 
H

Harlan Grove

Maileen wrote...
Via VBA i filtered a sheet. Basically i should have as result only 1 row.

How can i know what is the rowindex (figure in the grey column margin
e.g : 873, or 34245,...) ?
Click to expand...

Autofilter or advanced filter? If the former, it shouldn't be too
difficult to use a MATCH formula to find the matching cell. It's more
difficult with advanced filters.

Either way, you could use a formula to return the bottommost row of
filtered data. If the table were named tbl and the first column
contains no blank cells, try

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(tbl,ROW(tbl)-MIN(ROW(tbl)),0,1,1)),ROW(tbl))
 
Top