• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2293
  • Last Modified:

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
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 2
  • 2
1 Solution
 
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
 
NorieVBA ExpertCommented:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now