Find String in Range and hide all Rows in Range without String

Question

I try to search for a string in each row of a defined range. I do not know how to manage it. The target is to hide all rows where the search string was NOT found in the row.

Set ws = transferredSheet

Dim firstEmptyListRow  As Integer
Dim searchRange As Range
Dim i As Integer

" error thrown at next line"
firstEmptyListRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set searchRange = ws.Range(Cells(firstListRow, 1), Cells(firstEmptyListRow - 1, 8))

For i = firstListRow To firstEmptyListRow
   
    If Cells(i, 1).EntireRow.Find(What:=searchString, LookIn:=xlValues) > 0 Then
        Rows(i).EntireRow.Hidden = False
    Else
        Rows(i).EntireRow.Hidden = True
    End If
   
Next i

Answer 1

Match in All Columns

Find

Dim fCell As Range, lCell As Range

With searchRange
    ' Reference the last cell.
    Set lCell = .Cells(.Cells.CountLarge)
    ' Attempt to find a match.
    Set fCell = .Find(searchString, lCell, xlFormulas, xlWhole, xlByRows)
End With

If Not fCell Is Nothing Then ' match found
    
    ' Use this to exit the loop when the same cell is found again.
    Dim FirstAddress As String: FirstAddress = fCell.Address
    
    Dim urg As Range  

    Do
        ' Combine the found cell into a range.
        If urg Is Nothing Then
            Set urg = fCell
        Else
            Set urg = Union(urg, fCell)
        End If
        ' Find the next cell after the last cell in the row of the found cell.
        With Intersect(fCell.EntireRow, searchRange)
            Set fCell = searchRange.FindNext(.Cells(.Cells.Count))
        End With
    Loop Until fCell.Address = FirstAddress ' it's the first found cell
    
    ' Hide all rows.
    searchRange.Rows.Hidden = True
    ' Show matching rows.
    If Not urg Is Nothing Then urg.Rows.Hidden = False
    
End If

Application.Match

• If there are more rows to be shown, then use:

Dim urg As Range, rrg As Range

' In each row...
For Each rrg In searchRange.Rows
    ' ... attempt to find a match.
    If IsError(Application.Match(searchString, rrg, 0)) Then ' no match
       ' Combine the not matching row into a range.
       If urg Is Nothing Then Set urg = rrg Else Set urg = Union(urg, rrg)
    End If
Next rrg

' Show all rows.    
searchRange.Rows.Hidden = False
' Hide NOT matching rows.
If Not urg Is Nothing Then urg.Rows.Hidden = True

• If there are more rows to be hidden, then use:

Dim urg As Range, rrg As Range

' In each row...
For Each rrg In searchRange.Rows
    ' ... attempt to find a match.
    If IsNumeric(Application.Match(searchString, rrg, 0)) Then ' match found
       ' Combine the matching row into a range.
        If urg Is Nothing Then Set urg = rrg Else Set urg = Union(urg, rrg)
    End If
Next rrg

' Hide all rows.    
searchRange.Rows.Hidden = True
' Show matching rows. 
If Not urg Is Nothing Then urg.Rows.Hidden = False

Array

• This may possibly be more efficient. Share some feedback.

' Write the values from the range to an array.
Dim Data(): Data = searchRange.Value

Dim urg As Range, r As Long, c As Long

' Loop through the rows...    
For r = 1 To UBound(Data, 1)
    ' ... and the columns.
    For c = 1 To UBound(Data, 2)
        ' Compare the current element with the string.
        If StrComp(CStr(Data(r, c)), searchString, vbTextCompare) = 0 Then
            ' If it's a match , combine the matching row into a range.
            If urg Is Nothing Then
                Set urg = searchRange.Rows(r)
            Else
                Set urg = Union(urg, searchRange.Rows(r))
            End If
            Exit For ' it's found; continue with next row
        End If
    Next c
Next r

' Hide all rows.    
searchRange.Rows.Hidden = True
' Show matching rows. 
If Not urg Is Nothing Then urg.Rows.Hidden = False