How to select first visible row after Autofilter?

Question

In Excel VBA how can I select first visible row, cell "E" after applying autofilter.

I'm using this code:

Dim FindString As String
Dim Rng As Range

FindString = Trim(Cells(1, 2)) 'Cell B1

If FindString = "" Then
    ShowAllData
Else
    Range("C3").Select
    
    ActiveSheet.Range("$A$2:$F$14480").AutoFilter Field:=3
    ActiveSheet.Range("$A$2:$F$14480").AutoFilter Field:=3, Criteria1:="=*" & FindString & "*", Operator:=xlAnd
    If FindString <> "" Then
        'HERE it not works >>>>>
        Columns(6).Cells.SpecialCells(xlCellTypeVisible).Cells(6).Select
        '>>>>>
    End If
End If

What does 'select first visible row, cell "E"' should mean? Do you want selecting the second filtered row, or the cell "E" on this second row? — FaneDuru, Jun 18 '21 at 12:31

score 3 · Answer 1 · answered Jun 18 '21 at 12:54

3

Assuming that your header row is on row 2:

If FindString = "" Then
    ShowAllData
Else
    With ActiveSheet.Range("$A$2:$F$14480")
        .AutoFilter Field:=1
        .AutoFilter Field:=1, Criteria1:="=*" & FindString & "*", Operator:=xlAnd
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select
    End With
End If

The code uses offset(1, 0) to skip the header line.

Instead of .Cells(1, 5), you could use also .Cells(1, "E") or .Cells(5).

Note that you need Select to set the active cell for the user. Obligatory link to How to avoid using Select in Excel VBA

answered Jun 18 '21 at 12:54

FunThomas

23,043
3
18
34

1

@FaneDuru: I don't understand, what do you mean? As long as the first found row if part of the Range specified in the `With`-Clause, it is selected. If no hit was found, the first empty row below the last real used row is selected. This works even if data contains some empty row. Which case do you have in mind? – FunThomas Jun 18 '21 at 14:38
I simple was wrong. It looked for me that the code uses Offset of the filtered SpecialCells(xlCellTypeVisible) cells. Not to the original range and extract visible cells after... I will delete my comment. – FaneDuru Jun 18 '21 at 18:13

score 2 · Answer 2 · answered Jun 18 '21 at 12:52

Please, try the next code:

Sub SelectSecondFiltRng()
  Dim FindString As String, rng As Range, a As Range, c As Range, i As Long

 FindString = Trim(cells(1, 2).value) 'Cell B1

 If FindString = "" Then
    ShowAllData
 Else
    ActiveSheet.AutoFilter.ShowAllData 'remove the previous filter
    ActiveSheet.Range("$A$2:$F$14480").AutoFilter field:=3
    ActiveSheet.Range("$A$2:$F$14480").AutoFilter field:=3, Criteria1:="=*" & FindString & "*", Operator:=xlAnd
    If FindString <> "" Then
         On Error Resume Next 'for the case when nothing has been found by filter
          Set rng = ActiveSheet.Range("$A$2:$F$14480").SpecialCells(xlCellTypeVisible)
         On Error GoTo 0
         If Not rng Is Nothing Then
                For Each a In rng.Areas
                    For Each c In a.rows
                        i = i + 1
                        If i = 2 Then c.EntireRow.Select: Exit Sub
                    Next
                Next
        End If
    End If
 End If
End Sub

How to select first visible row after Autofilter?

2 Answers2