0
250
Viewsdeleting entire rows if value is found in a list vba
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```
2 answers
Answer question0
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
0
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.
