Looping within 4 columns to find an exact match of my array

Question

I am working on a new Excel macro through VBA.

The idea of the macro is to hide all the rows that doesn't contain an exact match of the string provided. By exact match, I mean it should only be that. In this macro's case, the cell should ONLY contain "HPS" or "ＨＰＳ". No mix of other words with it.

My range will be all visible cells under columns C, D, E, F. Can loops jump from 1 column to another?

This is my attempt code.

'②
'HPS有りエンドユーザー
    Dim userHPS As Range
    Dim criteriaArray As Variant
    Dim filteredRange As Range
    Dim i, lCell As Long
    Dim match As Boolean
    
    lastRow = Cells(3, "G").End(xlDown).row
    Set userHPS = Range("C3:F" & lastRow).SpecialCells(xlCellTypeVisible)
    criteriaArray = Array("HPS", "ＨＰＳ")
    match = True
    
    Dim i As Integer, icount As Integer
    'Dim FoundCell As Range, rng As Range
    'Dim myRange As Range, LastCell As Range
    
    'Set myRange = Range("C3:F" & lastRow).SpecialCells(xlCellTypeVisible)
    'Set LastCell = myRange.Cells(myRange.Cells.Count)
    'Set FoundCell = myRange.Find(What:=criteriaArray)
    
    For i = 3 To lastRow
        If InStr(1, Range("C3:F" & i), criteriaArray) > 0 Then
            icount = icount + 1
        End If
    Next i
    
    'If icount > 1 Then
        'We will hide it so we can leave all rows containing "HPS" on the sheet. (EntireRow.Hidden = True)
    'End If

Answer 1

With help of Excel application functions, we can solve the task in a single loop only:

Sub Test()
  HideFoundRows [C3:F21], Array("HPS", "H P S")
End Sub

Sub HideFoundRows(rng As Range, arr As Variant)
  Dim a As Range, r As Range
  With Application
    For Each r In rng.Rows
      If .Sum(.CountIf(r, arr)) Then
        If a Is Nothing Then
          Set a = r
        Else
          Set a = Union(a, r)
        End If
      End If
    Next
  End With
  a.EntireRow.Hidden = True
End Sub

The input range can have hidden rows.

.Sum(.CountIf(r, arr)) gives 0 (which treats as False) if nothing of arr is found in r otherwise it results the number of occurrences which treats as True.

All found rows are joined into the single reference and finally hide.