VBA Dynamic array has a bunch of empty values

Question

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.

@BigBen I did not make any changes to the sheet, just how the array is defined — Bert328
– Bert328, Commented Nov 10, 2022 at 15:07
Oh sorry, I misread and didn't realize the array values are from a separate worksheet. — BigBen
– BigBen, Commented Nov 10, 2022 at 15:09

BigBen · Accepted Answer · 2022-11-10 15:10:19Z

2

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.

answered Nov 10, 2022 at 15:10

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2022-11-10 23:29:17Z

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.

Collectives™ on Stack Overflow

VBA Dynamic array has a bunch of empty values

2 Answers 2

Comments

Reference Non-Blanks

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Reference Non-Blanks

Comments

Your Answer

Sign up or log in

Post as a guest

Related