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
PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
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

0
PeterBaileyUkAuthor Commented:
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
0
James ElliottManaging DirectorCommented:
Sorry, that is just me creating the initial array with some elements to test. If your AliasArray already has elements then you can remove the line. Something like this.

Option Explicit


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



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

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

AliasArray= arrTmp

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieData ProcessorCommented:
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.
0
PeterBaileyUkAuthor Commented:
brilliant had to ask or i wont learn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.