We help IT Professionals succeed at work.

# Remove array elements containing NULL values

on
I have an array like this:

myarray(n,3)

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...

Thanks

David
Comment
Watch Question

## View Solution Only

BRONZE EXPERT

Commented:
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
Next

' 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

Cheers
BRONZE EXPERT
Top Expert 2012

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

Anthony
SILVER EXPERT
Top Expert 2014
Commented:
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
Next
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

Commented:
Many thanks to you all!

David