Finding Row Number of A Value Within A Filtered Range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Suppose I have a filtered database ...
VBA Code:
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi Ark68,

the number of the row on the sheet

VBA Code:
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

VBA Code:
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
 
Last edited:
Upvote 0
Solution
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.
 
Upvote 0
Hi Ark68,

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

VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
Hi Ark68,

this is working for me:

VBA Code:
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
 
Upvote 0
Thanks Holger for your patient support.
From my simple testing, this appears to be working well! Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top