I have a filter macro that will filter a table for items and delete rows with that item. This is done by looping through an array which referenced a finite range on reference worksheet.

I am trying to change this array to be dynamic so that I can add or remove items to be deleted without having to open the code.

Before:

    Dim ArrCategory   As Variant
    ArrCategory = Worksheets("Sheet1").Range("B8:B12")
    For i = 2 To LastRowA
        For Each item In ArrCategory
            If Range("E" & i).Value = item Then
                lo1710.Range.autofilter Field:=5, Criteria1:=item
                Application.DisplayAlerts = False
                lo1710.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
                Application.DisplayAlerts = True
                lo1710.autofilter.ShowAllData
            Else
            End If
            
        Next item
    Next i

After:

    Dim ArrCategory   As Variant
    ArrCategory = Worksheets("Sheet1").Range("B8:B" & Cells(Rows.Count, "B").End(xlUp).row)
    For i = 2 To LastRowA
        For Each item In ArrCategory
            If Range("E" & i).Value = item Then
                lo1710.Range.autofilter Field:=5, Criteria1:=item
                Application.DisplayAlerts = False
                lo1710.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
                Application.DisplayAlerts = True
                lo1710.autofilter.ShowAllData
            Else
            End If
            
        Next item
    Next i

After making this change I started getting the "No cells were found" error. When I look in the locals window to see what is in that array, I see the values that are supposed to be in there, but then also hundreds of "empties".

The code does work to eliminate the rows containing the items in the array.

CodePudding user response：

Avoid implicit ActiveSheet references. Your Rows and Cells calls implicitly reference the active sheet, which is not guaranteed to be Worksheets("Sheet1").

Change

ArrCategory = Worksheets("Sheet1").Range("B8:B" & Cells(Rows.Count, "B").End(xlUp).row)

to

With Worksheets("Sheet1")
    ArrCategory = .Range("B8:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
End With

and note the . in front of Range, Cells, and Rows.

CodePudding user response：

Reference Non-Blanks

• If there are blank but not empty cells below your data, you could use the following function to reference the correct range.
• It uses the Find method and will fail
  • if the worksheet is filtered,
  • or if there are hidden rows.

The Function

Function SetNonBlankColumn( _
    FirstCell As Range) _
As Range
    Dim rg As Range
    With FirstCell.Cells(1)
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row   1) _
            .Find("*", , xlValues, , , xlPrevious)
        If lCell Is Nothing Then Exit Function
        Set rg = .Resize(lCell.Row - .Row   1)
    End With
    Set SetNonBlankColumn = rg
End Function

Usage

• It is assumed that the column range has at least two rows.

• In your code, you could utilize the function in the following way:

  Dim rg As Range: Set rg = SetNonBlankColumn(Worksheets("Sheet1").Range("B8"))
  Dim ArrCategory As Variant: ArrCategory = rg.Value
  

• Also, your worksheet is not qualified, and 'the code will look for it' in the active workbook which may be the wrong one (fix with e.g. ThisWorkbook.Worksheets...).

• Of course, if the blank but not empty cells are there by accident, select the cell below the last value and hold Ctrl Shift and press Down as many times as needed to hit the bottom row, release, and press Del to clear the range and continue using your code with the corrections suggested by BigBen.