Solved

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

Posted on 2010-08-28
2
920 Views
Last Modified: 2012-05-10
Hi there,

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

Thanks,
0
Comment
Question by:Quickstepbr
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33551217
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 33551233
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now