Clearing (not erasing or deleting) contents of Variant array

[welchs101]

I have a variant array and i define it initially like the following:

test_array = ws.Range("A1:A26").Value

I want to "clear" the contents of the array and start over populating it with other values. But i want the qty of entries in the array to stay the same.....and the data type as well.

Option Base 1


Sub test1()

Dim fl_name As String
Dim test_array As Variant

fl_name = ThisWorkbook.Name

Set ws = Workbooks(fl_name).Worksheets("Sheet1")

test_array = ws.Range("A1:A26").Value
junk1 = UBound(test_array)

For intIndex = 1 To UBound(test_array)
    Debug.Print test_array(intIndex, 1);
Next
Debug.Print

ReDim test_array(junk1) As Variant

For intIndex = 1 To UBound(test_array)
Debug.Print test_array(intIndex, 1);
Next
Debug.Print

End Sub

i tried the following code but when it gets to the "2nd" For statement i get a run time error.




I found this code to clear the array on line. The code used a slightly different array type but i thought i could still use this with my variant type array. Her eis the orringal code which does not crash.

Sub test()
     
    Dim intArray() As Integer
    Dim intIndex As Integer
     
    ReDim intArray(3) As Integer
     
    intArray(1) = 1
    intArray(2) = 2
    intArray(3) = 3
     
    For intIndex = 1 To 3
        Debug.Print intArray(intIndex);
    Next
    Debug.Print
     
     ' clear the array
    ReDim intArray(3) As Integer
     
    For intIndex = 1 To 3
        Debug.Print intArray(intIndex);
    Next
    Debug.Print
     
End Sub

[Steffen Thomsen]

Hi,

try changing this

ReDim test_array(junk1) As Variant

To this

ReDim test_array(1 to junk1) As Variant

Steffen Thomsen

[welchs101]

nope.....that did not work either............i think it might have something to do with this array being defined as variant and the other one as an integer......not sure........

[Steffen Thomsen]

Hi,

If you set the array later on with a range then try waiting to then with the redim

Then use

redim test_array(1 to Range("yourrange").Rows.Count) as variant

This way you just overwrite the values in the array with the values of the new range

Steffen Thomsen

[welchs101]

Steffen Thomsen good suggestion. However, i really wanted to just know how to "clear" the contents of the array........but i really like your suggestion.

I figured out my problem...........my array is 2 dimensional so i had to do a redim in the following manner

ReDim test_array(26,1) as variant

if you do this the arrays contents get zeroed out.

[shg]

1. Redim resets the elements of a Variant array to Empty, not 0.

2. Unless you have set Option Base 1, your code redimensions the array to (0 to 26, 0 to 1)

But I can't envision why this would be necessary.