Use AutoFilter on the current visible data

Question

To set Autofiler data on the currently active sheet, use the code below. It works, but all the concealed rows are shown once I run auto filter on any column. My goal is to set the filter on value and use a helper column. Thanks in advance for any assistance.

Option Explicit
Option Compare Text

    Sub AutoFilter_on_visible_data()
    
         Dim ws As Worksheet, arr, i As Long, lastR As Long, HdRng As Range, rng As Range
    
         Set ws = ThisWorkbook.ActiveSheet
         lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
          
         arr = ws.Range("A3:R" & lastR).Value2                          'Place the relevant columns in an array for faster iteration
         
          For i = 1 To UBound(arr)
          
            If ws.Rows(i + 2).Hidden = False Then                       '(i + 2) because Data starts at Row_3
            
                If Not arr(i, 2) Like "*Oil*" And _
                   Not arr(i, 5) Like "*-SYS-14" And _
                   Not arr(i, 6) Like "*Oil" Then
                   
                   addToRange HdRng, ws.Range("A" & i + 2)               'Make a union range of the rows NOT matching criteria...
                      
                End If
             End If
           Next i
           
          Application.ScreenUpdating = False
             If Not HdRng Is Nothing Then HdRng.EntireRow.Hidden = True      'Hide not matching criteria rows.
          Application.ScreenUpdating = True
    End Sub
    
    Private Sub addToRange(rngU As Range, rng As Range)
        If rngU Is Nothing Then
            Set rngU = rng
        Else
            Set rngU = Union(rngU, rng)
        End If
    End Sub 

Answer 1

Use the following modified code. It's assumed that the second row's headers are present:

Sub AutoFilter_on_visible_data()
         Dim ws As Worksheet, arr, i As Long, lastR As Long, lastCol As Long, arrH, rngH As Range, rng As Range
         Const helpH As String = "HelpColumn"
         
         Set ws = ThisWorkbook.ActiveSheet
         lastR = ws.Range("A" & ws.rows.count).End(xlUp).row
         lastCol = ws.cells(2, ws.Columns.count).End(xlToLeft).column 'last column, supposing that the header exists on the second row
         
         Set rngH = ws.rows(2).Find(what:=helpH, LookIn:=xlValues, Lookat:=xlWhole)
         If Not rngH Is Nothing Then  'if the helper header exists:
            lastCol = rngH.column
        Else                          'if not it is defined:
            lastCol = lastCol + 1
            ws.cells(2, lastCol).Value = helpH
        End If
        
        Set rng = ws.Range(ws.cells(2, 1), ws.cells(lastR, lastCol)) 'to use it for filterring
        
         If Not ws.AutoFilterMode Then rng.AutoFilter 'autofilter the resized range
        ws.AutoFilter.ShowAllData
        
         arr = ws.Range("A3:R" & lastR).Value2                      'Place the relevant columns in an array for faster iteration
         ReDim arrH(1 To UBound(arr), 1 To 1)
         
          For i = 1 To UBound(arr)
             If ws.rows(i + 2).Hidden = False Then                 '(i + 2) because Data starts at Row_3
            
                If Not arr(i, 2) Like "*Oil*" And _
                    Not arr(i, 5) Like "*-SYS-14" And _
                    Not arr(i, 6) Like "*Oil" Then
                   
                    arrH(i, 1) = "HH"                                        'Make a helper array to filter on it.
                   
                 End If
              End If
           Next i
           'Drop the arrH content at once:
           ws.cells(3, lastCol).Resize(UBound(arrH), 1).Value2 = arrH
           
           'Filter on the helper column:
           rng.AutoFilter field:=lastCol, Criteria1:="HH", Operator:=xlFilterValues
End Sub