[RESOLVED] Delete Row from Variable Size Array

[DKenny]

In the attached I am copying a range into a variable sized (both # of Records and # of Columns) array. The one common thing with all my possible source ranges in that the 2nd column always contains a Boolean to show if that row/record is to be used in the current pass.

What I would like to do is delete the entire row from the array where that boolean is FALSE. - I figure I need to loop through all columns in the array for that row and deleted the record, but the delete is not working. Is there a better way to do this?

PS: Looping through the Range and determining which rows to add is not going to work, its way too slow.

Sub array_row_delete()
    Dim MyArray() As Variant
    Dim x As Integer, y As Integer
    Dim MyRange As Range
    
    Set MyRange = Range("LOB")
    
    MyArray = MyRange
    
    For x = UBound(MyArray) To 1 Step -1
        If Not MyArray(x, 2) Then
            For y = 1 To MyRange.Columns.Count
                'MyArray(x, y).Delete -- this is the piece that does not work
            Next y
        End If
    Next x
End Sub

[VBAhack]

You can't delete an element from an array - you can change the value but not delete it. The size of an array is fixed. An array can be resized using ReDim Preserve but you can't pick individual elements to eliminate. A practical solution is to create another array of the same size of the original, loop through the original and copy valid items to the new array, keeping track of the element number, then re-size the 2nd array with ReDim Preserve when you are done. Or, loop through the original array to find out how many valid items you have, then create the 2nd array to the correct size, then loop again through the original array and copy the valid elements. What you are really describing is a linked list that can have elements deleted - can't do that with arrays. Actually, if the size isn't so big, you might consider the built-in data structure Collection.

VBAhack