How do I delete all "empty" elements of my array

Hi there,

I need to delete all "empty" elements of my array.
How do I do this?

Thanks,
QuickstepbrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
cyberkiwiConnect With a Mentor Commented:
Try this
Function DeleteEmpty(ByRef Arr As Variant) As Variant
If Not IsArray(Arr) Then Exit Function

Dim i As Long
Dim acount As Long
Dim arrCopy()
ReDim Preserve arrCopy(UBound(Arr))

acount = 0
For i = LBound(Arr) To UBound(Arr)
If Arr(i) & "" > "" Then
    arrCopy(acount) = Arr(i)
    acount = acount + 1
End If
Next

ReDim Preserve arrCopy(acount - 1)
DeleteEmpty = arrCopy
End Function

Sub x()
Dim ars(5) As String
ars(1) = "test"
ars(2) = ""
ars(3) = ""
ars(4) = "blah"
ars(5) = "blah"
MsgBox Join(DeleteEmpty(ars), ",")

End Sub

Open in new window

0
 
zorvek (Kevin Jones)ConsultantCommented:
An alternative:

    Y = "|" & Join(X, "|") & "|"
    Do: Y = Replace(Y, "||", "|"): Loop While Len(Replace(Y, "||", "|")) <> Len(Y)
    X = Split(Mid(Y, 2, Len(Y) - 2), "|")

X is the array (a Variant) and Y is a string.

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.