Selecting First AutoFilter Row

  • Thread starter Thread starter Brian Gibson
  • Start date Start date
B

Brian Gibson

How do I select the first filter row of an autofiltered range?

I tried going to the top of the range and doing an offset:

range("E1").Activate
ActiveCell.Offset(1, 0).Activate

but that did not work.

If E1 was the header row and E150 was the first filtered row, this code
above would activate E2.
 
The following code, adapted from one of Dave Peterson's postings will
select the first row of data. Change the Resize at the end to
accommodate your number of columns:
'========================================
Sub ActivateFirstRow()
'by Dave Peterson
'revised last line to select first row

Dim curWks As Worksheet
Dim Rng As Range

Set curWks = ActiveSheet

If Not curWks.AutoFilterMode Then
MsgBox "Please apply a filter"
Exit Sub
End If

If Not curWks.FilterMode Then
MsgBox "you haven't filtered anything"
Exit Sub
End If

Set Rng = curWks.AutoFilter.Range

On Error Resume Next
Set Rng = Nothing 'overkill if rng1 never used before
With curWks.AutoFilter.Range
'just first column of filtered range
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "Filter showed nothing"
Exit Sub
End If

Rng.Rows(1).Resize(1, 8).Activate

End Sub
'=============================================
 
Here is a code snippet you can put in your code.

Sub SelectFirst()
Dim rng As Range
Dim rng1 As Range, rng2 As Range
Set rng2 = ActiveSheet.AutoFilter.Range
Set rng = rng2.Columns(1).Cells
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Resize(1, rng2 _
.Columns.Count).Select
Else
MsgBox "No visible rows"
End If
End Sub
 
Back
Top