CODEWITHSUNDEEP
vbaexcel
Digital Xanatos
Digital Xanatos

Reputation: 63

Getting Values from a Column into an array when using AutoFilter Mode and then matching the array to delete rows

I'm trying to Save Values in Column D as an Array so I can check, after the autofilter is removed, for anything matching what is in the Array in Column D and deleting those rows.

I couldn't filter by the array. I believe that is because the array is made of numbers, as Column D is all ID numbers, instead of a string.

Right now I'm getting Subscript out of range at Selection.Value = PackRemov part.

With ActiveSheet
    Set rNumberColumn = .Range("N:N")
    rNumberColumn.AutoFilter 1, Criteria1:="<>"
    PackRemov = .Range("D:D").Value
    ActiveSheet.AutoFilterMode = False

    For X = 1 To LRD
        Range("D" & X).Select
        If Selection.Value = PackRemov(i) Then
            Selection.EntireRow.Delete
        End If
    Next X

If this doesn't work then is there a way I can maybe do I ".Find" to match whatever is in the array and delete those rows?

Any help would be appreciated! Thanks!

Upvotes: 2

Views: 127

Answers (2)

user4039065
user4039065

Reputation:

As mentioned in comments, .AutoFilter prefers an array of text-that-looks-like-numbers so CStr each element in the array.

Option Explicit

Sub wytrt()
    Dim arr As Variant, i As Long, dict As Object

    Set dict = CreateObject("scripting.dictionary")
    dict.comparemode = vbTextCompare

    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        arr = .Range(.Cells(2, "D"), .Cells(.Rows.Count, "N").End(xlUp)).Value2
        For i = LBound(arr, 1) To UBound(arr, 1)
            If arr(i, UBound(arr, 2)) <> vbNullString Then
                dict.Item(arr(i, LBound(arr, 2))) = CStr(arr(i, LBound(arr, 2)))
            End If
        Next i

        With .Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp))
            .AutoFilter Field:=1, Criteria1:=dict.items, Operator:=xlFilterValues

            'Debug.Print .Offset(1, 0).SpecialCells(.xlCellTypeVisible).Address(0, 0)
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Upvotes: 1

Ricardo A
Ricardo A

Reputation: 1815

When creating an Array with a Range VBA, if the range is by Column (like A1:A5) it creates a multidimensional array instead of a single Array.

For X = 1 To LRD
    Range("D" & X).Select
    If Selection.Value = PackRemov(i, 1) Then 'This should fix this portion
        Selection.EntireRow.Delete
    End If
Next X

Also, When deleting Rows you want to loop backwards, because if you are loping from 1 to X, if you are on example row 5, and you delete it, Row 6 becomes Row 5, but since you already processed row 5 you move to 6 (which was 7), so you skipped 1 row every time you delete one. You must do For X = LRD to 1 Step -1 to fix this.

P.S. You can also get rid of the line Range("D" & X).Select if you include it in the loop. Remove Selection.Value and just do Range("D" & X).Value and Range("D" & X).EntireRow.Delete

Upvotes: 1

Related Questions