deleting entire rows if value is found in a list vba-CodePudding

I have a list of around 200 names (on a sheet) that I need to try and filter (and delete) out of a data sheet. I'm struggling to get down how to set the list of names as an array so that I can filter that array under Range("E:E").AutoFilter Field:=1, Criteria1:=**Array Here**, _ and then later entirerow.delete.

This is my most recent attempt based off of other sources online, but it seems that most of them are lists that only contain 4-5 values, and I'm struggling to find anything that would be useful in putting all of the values in an array and filtering them based off of that, any help/workarounds are appreciated thank you!

Call myArrayRange
    Dim rng As Range
    Dim pos As Integer
    Dim arr As String
    Set arr = Worksheets("control").Range("K2:K10000")
    Set sht = ws
    With sht
        Range("E:E").AutoFilter Field:=1, Criteria1:=Array(""), _
                Operator:=xlFilterValues
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & LstRw).SpecialCells(xlCellTypeVisible)
        rng.EntireRow.Delete
        .AutoFilterMode = False
    End With

End Sub
Sub myArrayRange()
lr = Worksheets("Control").Cells(Rows.Count, 11).End(xlUp).Row
Dim iAmount() As Variant
Dim iNum As Integer
iAmount = Range("K2:K" & lr)

For iNum = 1 To UBound(iAmount)
    Debug.Print iAmount(iNum, 1)
Next iNum

End Sub```

CodePudding user response：

EDIT: updated to match your actual use case.

Here's a basic example of how you can do it:

Sub Tester()
    
    Dim arr, rngNames as range, ws As Worksheet
    
    Set ws = ActiveSheet 'for example: the sheet with the data to filter
    
    With ws.Parent.Worksheets("Control")
        Set rngNames = .Range("K2:K" & .Cells(.Rows.Count, "K").End(xlUp))
    End With
    arr = RangeToArray(rngNames) 'get an array from the list of names

    ws.Range("E:E").AutoFilter Field:=1, Criteria1:=arr, _
                               Operator:=xlFilterValues
    ws.Autofilter.Range.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ws.AutoFilterMode = False
     
End Sub

'convert a range to a zero-based 1D array
Function RangeToArray(rng As Range)
    Dim r As Long, c As Long, arr, data, i
    data = rng.Value                    'get the source data
    ReDim arr(0 To rng.Cells.Count - 1) 'size the output array
    For r = 1 To UBound(data, 1)        'loop over the data from the range
        For c = 1 To UBound(data, 2)
            arr(i) = data(r, c)
            i = i   1
        Next c
    Next r
    RangeToArray = arr
End Function

CodePudding user response：

This is a really dangerous way to delete things. You can't really recover the data so make sure that filter works.

Sub Button1_Click()
    myArrayRange
End Sub

Sub myArrayRange()
    Dim rng As Range
    Dim pos As Integer
    Dim sht As Worksheet

    Set sht = ActiveSheet
    With sht
         'Your string array that holds names would go here VVVV (According to MS Docs)
         Range("E:E").AutoFilter Field:=1, Criteria1:=Array(""), _
            Operator:=xlFilterValues
         LstRw = .Cells(.Rows.Count, "A").End(xlDown).Row
         Set rng = .Range("A2:A" & LstRw).SpecialCells(xlCellTypeVisible)
         rng.EntireRow.Delete
         .AutoFilterMode = False
    End With
End Sub

You'll have to find the sheet you need some way. I used the active sheet. Here's a screenshot of the data before and after.