In Excel Sheet how to Eliminate or Remove Filter and copy the selected records defined in another sheet using dynamic array list VBA Module

Question

I'm attempting to generate an Excel sheet with a dynamic array macro (VBA). I want to use a dynamic array list to reduce (remove or conceal) the number of records in the main Sheet "StatusReport" based on the data selected in the column "AlertCount." For instance, StatusReport (Worksheet)

Filter_Criteria (Worksheet)

Expected output :

All record should display without "1055" and "1056" related Alert Count (Eliminate Record)

But its removed all the records now instead of selected value

My Module as below it display the filter records only but I need to eliminate the selected filter records . VBA Module as below :

   Sub DeleteFilter_Data()
Set Data_sh = ThisWorkbook.Sheets("StatusReport")
Set Filter_Criteria = ThisWorkbook.Sheets("Filter_Criteria")
  
Data_sh.AutoFilterMode = False

Dim AlertCount_List() As String
Dim n As Integer

n = Application.WorksheetFunction.CountA(Filter_Criteria.Range("A:A")) - 1
ReDim AlertCount_List(n) As String
Dim i As Integer

For i = 0 To n
        AlertCount_List(i) = Filter_Criteria.Range("A" & i + 2)
Next i

Dim Arr01 As Variant
Dim i01 As Integer
Dim i02 As Integer

'Creates a list of everything in Column I, minus everything in Filter_Criteria list
Arr01 = Range("I2", Range("I2").End(xlDown))
For i01 = 1 To UBound(Arr01, 2)
    For i02 = 0 To n - 1
    If Arr01(i01, 1) = AlertCount_List(i02) Then
        Arr01(i01, 1) = ""
    End If
    Next i02
Next i01

'Turns list into strings (needed for the Filter command).
Dim ListEdited() As String
ReDim ListEdited(1 To UBound(Arr01, 1)) As String
For i01 = 1 To UBound(Arr01, 2)
    ListEdited(i01) = Arr01(i01, 1)
Next i01

'Filter command that keeps all entries except any found within the Filter_Criteria Sheet.

 Data_sh.UsedRange.AutoFilter 9, ListEdited(), xlFilterValues
 
End Sub

Please help me out with corrected Macro using dynamic array list.

narikkadan · Answer 1 · Nov 27, 2022

You want to maintain all Alert Counts aside from those shown on the Filter Criteria sheet, is I correct? This is done via the code below. If I didn't understand your queries correctly, do let me know so that I can try again.

EDIT 20210630: I have updated the below code.

Sub HideFilter_Data()
Set Data_sh = ThisWorkbook.Sheets("StatusReport")
Set Filter_Criteria = ThisWorkbook.Sheets("Filter_Criteria")
  
Data_sh.AutoFilterMode = False

Dim AlertCount_List() As String
Dim n As Integer

n = Application.WorksheetFunction.CountA(Filter_Criteria.Range("I:I")) - 1
ReDim AlertCount_List(n) As String
Dim i As Integer

For i = 0 To n
        AlertCount_List(i) = Filter_Criteria.Range("I" & i + 2)
Next i

Dim Arr01 As Variant
Dim i01 As Integer
Dim i02 As Integer

'Creates a list of everything in Column I, minus everything in Filter_Criteria list
Arr01 = Range("I2", Range("I2").End(xlDown))
For i01 = 1 To UBound(Arr01, 1)
    For i02 = 0 To n - 1
    If Arr01(i01, 1) = AlertCount_List(i02) Then
        Arr01(i01, 1) = ""
    End If
    Next i02
Next i01

'Turns list into strings (needed for the Filter command).
Dim ListEdited() As String
ReDim ListEdited(1 To UBound(Arr01, 1)) As String
For i01 = 1 To UBound(Arr01, 1)
    ListEdited(i01) = Arr01(i01, 1)
Next i01

'Filter command that keeps all entries except any found within the Filter_Criteria Sheet.
Data_sh.UsedRange.AutoFilter 9, ListEdited(), xlFilterValues

'Data_sh.UsedRange.AutoFilter 9, AlertCount_List(), xlFilterValues
'Data_sh.UsedRange.AutoFilter 9, Criteria1:="<> 1056"   ‘ This work fine but it's a  hard coded value

End Sub