Empty array at the end of the loop VBA Excel

Question

I have the below code that adds values to an array if it meets a criteria.

It keeps looping horizontally through the columns across a row and then repeats the same for the next row and so on.

I am trying to clear the values accumulated in the array and empty it at the end of the columns loop:

For a = 21 To 23

    Count = 0
    For b = 2 To 36
    If Not Worksheets("Sheet1").Cells(a, b).Value = "A" And Not Worksheets("Sheet1").Cells(a, b).Value = "B" Then
    Count = Count + 1
    Else
    If Not Count = 0 Then

    Dim arr() As Long

    arrLen = 1
    ReDim Preserve arr(1 To arrLen)
    arr(arrLen) = Count
    arrLen = arrLen + 1

    For i = LBound(arr) To UBound(arr)
        msg = msg & arr(i) & vbNewLine
    Next i

    Count = 0
    End If
    End If
    Next b

    MsgBox Worksheets("Sheet1").Cells(a, 1).Value & vbNewLine & msg
    Erase arr 'not working

Next a

As you can see from the above code, I get a msgbox displaying the values at the end of each loop, however as it continues, the array keeps getting bigger and bigger indicating that the Erase line is not working.

Kindly help!

You have 'arrLen=1' into the 'For ... Next', so at each loop arrLen become 1 — genespos
– genespos, Commented Feb 27, 2015 at 12:43

Gary's Student · Accepted Answer · 2015-02-27 12:31:29Z

2

You can either use a loop to set the array elements to nulls, or ReDim the array away:

Sub marine()
    Dim arr()
    ReDim arr(1 To 2)
    arr(1) = "Alpha"
    arr(2) = "Beta"

    ReDim arr(1 To 1)
    arr(1) = ""
End Sub

That way you can re-use the same array name later in the sub.

answered Feb 27, 2015 at 12:31

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CaptainABC Over a year ago

Thanks for the suggestion, I have tried to replace my Erase arr line with ReDim arr(1 To 1) followed by arr(1) = "" but I am getting an Error 13: Type mismatch. Any idea why it's happening?

Gary's Student Over a year ago

@CaptainABC that is because for you arr is Long ..............so use something like arr(1)=0

CaptainABC Over a year ago

By changing "" to 0, I am no longer getting the error, but the array is still not being cleared, maybe it's something to do with my arrLen variable?

CaptainABC Over a year ago

Silly me, the array was clearing fine! The problem was with me forgetting to also clearing msg variable :) All good now.

Collectives™ on Stack Overflow

Empty array at the end of the loop VBA Excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related