We help IT Professionals succeed at work.

Remove array elements containing NULL values

DrD asked
I have an array like this:


n is the number of rows

The array contains the ID of the customer, a series of dates, and the number of purchases

The elements 1, 2, and 3 are such that

1 is a fixed string value "Customer"

2 is a date such as "1/1/2001"

3 is a variant: the number of purchases, a null value or an integer

How do I remove the entire record (customer, date, and the number of purchases) from the array IF the number of purchases is 0 or a NULL value to produce a new (shorter) array with no NULLs at all...


Watch Question


First of all, I suggest to use UDT rather then 2D array:
Type PurchaseInfo
    Customer As String * 8
    DatePurchase As Date
    NumPurchases As Variant 'or As Integer
End Type
Dim pi() As PurchaseInfo

You can RedimPreserve nD arrays only by last dimension, so if you want to use 2D array, you have to change myarray(n,3) to myarray(3,n)

Function FilterZero(arr As Variant) As Long
    Dim index As Long, zeros As Long
    For index = LBound(arr) To UBound(arr)
'    For index = LBound(arr,2) To UBound(arr,2) 'for 2D array MyArray(3,n)
        If arr(index).NumPurchases = 0 Then
'        If arr(3,index) = 0 Then 'for 2D
            ' we've found a zero
            arr(index) = Empty
            zeros = zeros + 1
        ElseIf zeross Then
            ' if we've found one or more zeros so far
            ' we need to move elements towards lower indices
            arr(index - zeros) = arr(index)
            'For 2D
            'arr(1,index - zeros) = arr(1,index)
            'arr(2,index - zeros) = arr(2,index)
            'arr(3,index - zeros) = arr(3,index)
            arr(index) = Empty
        End If
    ' return the number of zeros
    FilterZero = zeros
End Function

Now in code:
say, you have array of UDTs or 2D array:
nZeros = FilterZero(pi) 'or FilterZero(myarray)
If nZeros Then
   Redim Preserve pi(LBound(pi) To UBound(pi)-nZeros)
' or Redim Preserve MyArray(1 To 3, LBound(MyArray,2) To UBound(MyArray,2)-nZeros)
End if

Top Expert 2012

Take a look at Collections.  While somewhat more difficult to set up, I believe they are more appropriate for what you are attempting.

Top Expert 2014
While I agree with ark about the UDT, their example code doesn't address Null values.  

Type PurchaseInfo
   Customer As String * 8
   DatePurchase As Date
   NumPurchases As Variant  'can NOT be Integer if Null values possible
End Type
Dim pi(1 to 25) As PurchaseInfo
Dim lngPi_Count as Long
lngPi_Count = 25

'Note: you can use ReDim to do dynamic allocation

Function FilterZeroNull(arr(), elements as Long) As Long
'This function moves non-zero and non-Null elements to the front
'of the arr array and returns the
 Dim lngPosn As Long
 Dim lngLoop As Long
 lngPosn = LBound(arr) - 1
 For lngLoop = LBound(arr) To elements
   Select Case True
     Case IsNull(arr(lngLoop).NumPurchases), arr(lngLoop).NumPurchases = 0
     Case Else
       lngPosn = lngPosn + 1
       arr(lngPosn) = arr(lngLoop)
   End Select
 FilterZeroNull = lngPosn
End Function

lngPi_Count = FilterZeroNull(pi, lngPi_Count)
If lngPi_Count < LBound(pi) then
  'code to handle condition for no elements
End If


Many thanks to you all!


Explore More ContentExplore courses, solutions, and other research materials related to this topic.