clear empty arrays in VBA

Question

0.00/5 (No votes)

See more:

Hi
I have an array in VBA, which adds empty string elements.

How can I delete the empty arrays?

And the Array is in a for-loop and I want to clear the array fully every time
How can I do that?

the "arr() = nothing" doesn't work

VB

For i = 1 To myfolder.Items.Count
  Set myItem = myfolder.Items(i)
  arr = Split(myItem.Body, vbCrLf)
  

  
  xlobj.Range("a" & i + 1).Value = myItem.SenderName
  xlobj.Range("b" & i + 1).Value = myItem.ReceivedTime
  xlobj.Range("c" & i + 1).Value = arr(1)
  xlobj.Range("d" & i + 1).Value = arr(3)
  xlobj.Range("e" & i + 1).Value = arr(5)
  xlobj.Range("f" & i + 1).Value = arr(7)
  xlobj.Range("g" & i + 1).Value = arr(9)
  xlobj.Range("h" & i + 1).Value = arr(11)
  
  arr() = noting

 Next

Posted 27-Mar-13 22:20pm

chenduran10

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Leo Chapiro · Answer 1 · 2013-03-27T22:30:00

Solution 1

Try this:

VB

Erase arr

Posted 27-Mar-13 22:30pm

Leo Chapiro

Comments

Maciej Los 28-Mar-13 4:48am

Good answer!
+5

Leo Chapiro 28-Mar-13 4:56am

Thank you, Maciej ! :)

OriginalGriff · Answer 2 · 2013-03-27T22:32:00

To remove an element from any array, you have to "shuffle" the remaining elements out, and then shrink the size of the whole array by one. Setting an element to Nothing does not do that, it just removes the element data without affecting the indexes. Personally, I wouldn't do it - I would create a new List of the relevant items, and loop though the array, adding items that I want to keep to the list. Then finally I would reload the array reference from the list with the List<T>.ToArray method:

VB

Dim list As New List(Of [MyClass])()
For Each m As [MyClass] In myArray
    If IWantToKeepThisOne(m) Then
        list.Add(m)
    End If
Next
myArray = list.ToArray()

Maciej Los · Answer 3 · 2013-03-27T22:55:00

Solution 3

Solution 1 (by du[DE]) is very good, but need some explanation.

Erase method frees the memory used by dynamic arrays. Before your program can refer to the dynamic array again, it must redeclare the array variable's dimensions using a ReDim statement.

Posted 27-Mar-13 22:55pm

Maciej Los

Comments

chenduran10 28-Mar-13 5:12am

Why I can't do the ReDim statement in this code?

Maciej Los 28-Mar-13 5:18am

Can't you? You can!
In your code you don't need to re-declare a size of array, because Split() function[^] do it in each step of for-next loop.
You should erase array outside of loop.

Leo Chapiro 28-Mar-13 5:12am

Another +5 for Maciej for this really good explanation! :)