How do I select the first cell of a filtered list?
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. -- Shane Moore ------------------------------------------------------------------------ Shane Moore's Profile: http://www.excelforum.com/member.php...o&userid=36421 View this thread: http://www.excelforum.com/showthread...hreadid=571682 |
How do I select the first cell of a filtered list?
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.) Shane Moore wrote: 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. -- Shane Moore ------------------------------------------------------------------------ Shane Moore's Profile: http://www.excelforum.com/member.php...o&userid=36421 View this thread: http://www.excelforum.com/showthread...hreadid=571682 -- Dave Peterson |
How do I select the first cell of a filtered list?
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 "Shane Moore" wrote in message ... 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. -- Shane Moore ------------------------------------------------------------------------ Shane Moore's Profile: http://www.excelforum.com/member.php...o&userid=36421 View this thread: http://www.excelforum.com/showthread...hreadid=571682 |
All times are GMT +1. The time now is 02:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com