asked on

remove array element in access vba

I wish to remove an element from the array that contains " " or "" I know it will involve a redim maybe a preserve too but not sure how to remove at the right place

so far:

For index = LBound(AliasArray) To UBound(AliasArray)
If AliasArray(index) = " " Or AliasArray(index) = "" Then remove that element

Next index

James Elliott

You can use a temporary array.

Option Explicit

Sub EE()

Dim arr As Variant
Dim arrTmp() As Variant
Dim x As Long, counter As Long

arr = Array(1, 2, 3, " ", 4)

For x = LBound(arr) To UBound(arr)

    If Not (arr(x) = "" Or arr(x) = " ") Then
    
        ReDim Preserve arrTmp(counter)
        
        arrTmp(counter) = arr(x)
        
        counter = counter + 1
        
    End If
Next x

arr = arrTmp

End Sub

Open in new window

PeterBaileyUk

ASKER

that looks great could you teach me about this part :

arr = Array(1, 2, 3, " ", 4)

i can see its making arr equal to array but the bits within the brackets i am not sure about

ASKER CERTIFIED SOLUTION

James Elliott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

How are you creating the array in the first place?

If it's through code using Redim etc then perhaps you could add something to the code that creates the array to exclude the values/strings/whatever you don't want in the array.

PeterBaileyUk

ASKER

brilliant had to ask or i wont learn