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

0 votes

 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)

enter image description here

Filter_Criteria (Worksheet)

enter image description here

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

enter image description here

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.

Nov 27, 2022 in Others by Kithuzzz
• 38,010 points
348 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Others

0 votes
1 answer

Converting Textboxes Link and/or Formula to Values in a Copied Sheet using Excel VBA

Copy the values using Range and Value2 With ActiveSheet.UsedRange ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,420 points
344 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,104 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
803 views
0 votes
1 answer
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,420 points
1,260 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
686 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,386 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,241 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
957 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP