Finding Row Number of A Value Within A Filtered Range

[Ark68]

Suppose I have a filtered database ...

Dim intseg as integer
Dim intrw as integer
With ws_segments
     If .AutoFilterMode = True Then .AutoFilterMode = False
     .Range("A1").AutoFilter Field:=32, Criteria1:=corrval
End With

How would I go about finding the row number within the filtered data of a specific value represented by variable 'intseg' in column 5. For example, what row (variable 'intrw'), if any, is intseg = 2 in? If it doesn't exist in any row, variable 'intrw' needs to equal 0.

 

[HaHoBe]

Hi Ark68,

the number of the row on the sheet

Dim lngItem As Long
Dim lngRowFound As Long

Const cstrColSearch As String = "E"
'...

'...
With ws_segments
    If .AutoFilterMode = True Then .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=32, Criteria1:=corrval
    lngRowFound = Application.Match(lngItem, .Range(.Cells(1, cstrColSearch), .Cells(.Rows.Count, cstrColSearch).End(xlUp)), 0)
    If .Rows(lngRowFound).Hidden = True Then lngRowFound = 0
End With
'...

Above code will look for the match and get the row number, second step is to check if row is hidden.

Position of item in filtered data if visible

Dim rngCell As Range
Dim lngIntern As Long
Dim lngRowFound As Long
Dim lngItem As Integer

Const cstrColSearch As String = "E"
'...

'...
lngRowFound = 0
With ws_segments
  If .AutoFilterMode = True Then .AutoFilterMode = False
  .Range("A1").AutoFilter Field:=32, Criteria1:=corrval
  For Each rngCell In .Range(.Cells(2, cstrColSearch), .Cells(.Rows.Count, cstrColSearch).End(xlUp)).SpecialCells(xlCellTypeVisible)
    lngIntern = lngIntern + 1
    If rngCell.Value = lngItem Then
      lngRowFound = rngCell.Row
      Exit For
    End If
  Next rngCell
End With
If lngRowFound > 0 Then
  MsgBox lngItem & " found in Row " & lngRowFound & vbCrLf & _
        "being on Row " & lngIntern & " of the filtered data", _
        vbInformation, "Found item"
Else
  MsgBox "Item not found", , "no match"
End If

Loop through each visible cell in the given column, assign Value if match and quit loop.

HTH
Holger

 

[Ark68]

Hi Holger! Thank you for helping me find a solution. The first was all I needed, but I enjoyed figuring out what the second code was doing.

 

[HaHoBe]

Hi Ark68,

the first code will work as long as the item is found. You better use this altered code instead

Dim lngItem As Long
Dim lngRowFound As Long
Dim varMatch As Variant

Const cstrColSearch As String = "E"
'...

'...
lngRowFound = 0
With ws_segments
    If .AutoFilterMode = True Then .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=32, Criteria1:=corrval
    varMatch = Application.Match(lngItem, .Range(.Cells(1, cstrColSearch), .Cells(.Rows.Count, cstrColSearch).End(xlUp)), 0)
    If Not IsError(varMatch) Then
      If .Rows(varMatch).Hidden = False Then lngRowFound = varMatch
    End If
End With
'...

Ciao,
Holger

 

[Ark68]

Thank you, I will make the adjustment. It will be unlikely the value won't be found, but things do happen.

If I can expand on this a bit Holger, and you don't mind sharing your knowledge, how can this code be adapted so that instead of find the row number of a match to a particular value, we find the row based on the match of the MAX number in column 6 of the filtered data base?

 

[HaHoBe]

Hi Ark68,

this is working for me:

Sub MrE1222022_FindRowMax()
Dim lngRowFound As Long
Dim varMatch As Variant
Dim rngTotal As Range
Dim rngVisible As Range

Const cstrColMax As String = "F"
'...

'...
lngRowFound = 0
With ws_segments
    If .AutoFilterMode = True Then .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=32, Criteria1:=corrval
    'total range for Column F
    Set rngTotal = .Range(.Cells(1, cstrColMax), .Cells(.Rows.Count, cstrColMax).End(xlUp))
    'visible range for Column F
    Set rngVisible = rngTotal.SpecialCells(xlCellTypeVisible)
    'using Application.Match with the Max of the visible cells
    varMatch = Application.Match(WorksheetFunction.Max(rngVisible), rngTotal, 0)
    Set rngVisible = Nothing
    Set rngTotal = Nothing
    If Not IsError(varMatch) Then
      If .Rows(varMatch).Hidden = False Then lngRowFound = varMatch
    End If
End With
End Sub

Ciao,
Holger

 

[Ark68]

Thanks Holger for your patient support.
From my simple testing, this appears to be working well! Thanks again.