How can i obtain the row number of the first item in a filtered table with VBA Excel

Question

I have the following in the attachment

i tried to use the following macro to obtain the number of row of the first item when i filter this list to "Retail" only. the macro should show a message box with the number "3" according to the firt "retail" is in row 3, but ot keeps sending me the number "1"

Sub Macro1()

    Range("A1").Select
    SendKeys "{DOWN}"
    FirstRow = ActiveCell.Row
    
    MsgBox (FirstRow)
    
End Sub

the message box should say "3"

Answer 1

Try this code (assuming data is in Column A):

Sub FirstVisibleRow()
   MsgBox Columns("A").SpecialCells(xlCellTypeVisible).Find("Retail").Row
End Sub

If you need the first row which is visible, you can use also Find("*")

Answer 2

N.B. This function will only work if you are using an AutoFilter.

Function GetFirstItem(ws As Worksheet) AS Long
    GetFirstItem = -1 'Return -1 if there are no items
    If Not ws.AutoFilterMode Then Exit Function 'Return -1 if there is no AutoFilter

    Dim lHeader AS Long, lFirstItem AS Long, rArea As Range

    lHeader = ws.AutoFilter.Range.Cells(1, 1).Row 'Store the Header Row
    lFirstItem = -1 ' Return -1 if there are no items

    'Loop through the visible Contiguous Areas - i.e. the rows you have filtered for    
    For Each rArea In ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas
        If rArea.Cells(1, 1).Row > lHeader Then 'If this Area does not contain the Header
            If lFirstItem < 1 Or rArea.Cells(1, 1).Row < lFirstItem Then
                'Store the Item Row if it is the new Lowest
                lFirstItem = rArea.Cells(1, 1).Row
            End If
        ElseIf rArea.Rows.Count > 1 Then 'Area contains Header and 1 or more Items
            If lFirstItem < 1 Or rArea.Cells(2, 1).Row < lFirstItem Then
                'Store the Item Row if it is the new Lowest (Ignore the Header row)
                lFirstItem = rArea.Cells(2, 1).Row
            End If
        End If
    Next rArea
End Function

Answer 3

Another option which uses MATCH function:

It is not necessary to filter the range to retrieve the result.

Sub firstRetailRow()

Dim rgCheck As Range
With ActiveSheet.Range("A1").CurrentRegion
    Set rgCheck = .Offset(1).Resize(.Rows.Count - 1, 1)
End With

Dim rowID As Long
With Application.WorksheetFunction
    rowID = .IfError(.Match("Retail", rgCheck, 0), 0)
End With

MsgBox rowID

End Sub

You could use a formula as well to retrieve the row: =MATCH("Retail",A2:A15,0)

Answer 4

Get Worksheet Row (Number) of First Visible Row (Range)

Risky

Sub TestShort()
     
    Dim FirstRow As Long
    
    With ActiveSheet.AutoFilter.Range
        On Error Resume Next
            FirstRow = .Resize(.Rows.Count - 1).Offset(1) _
                .SpecialCells(xlCellTypeVisible).Row
        On Error GoTo 0
    End With
    
    MsgBox FirstRow
    
End Sub

Safe(r)(st)

Sub TestLong()
     
    Dim FirstRow As Long
    
    With ActiveSheet
        '.AutoFilterMode = False
        '.Range("A1").CurrentRegion.AutoFilter 1, "Retail"
        If .AutoFilterMode Then
            With .AutoFilter.Range
                On Error Resume Next
                    FirstRow = .Resize(.Rows.Count - 1).Offset(1) _
                        .SpecialCells(xlCellTypeVisible).Row
                On Error GoTo 0
            End With
'            If Not .FilterMode Then
'                MsgBox "The range is not filtered.", vbExclamation
'            End If
'        Else
'            MsgBox "The auto filter is turned off.", vbExclamation
        End If
    End With
    
    MsgBox FirstRow
    
End Sub