title: Array reset in Excel VBA
body: <p>I am using Erase to clear all elements in array. It is working fine but what I want is to reset the array to blank in loop so that every time it get data for that row and paste in the transpose. I am facing transpose limit issue so want to do one line at a time. How can I make sure every time in for loop I have new row data ONLY in my Vr(). in below is it making row data as empty and then append new row data.</p>
  <pre><code>For i = 2 To r
      For j = 3 To c
      Dim Vr() As Variant
     ' Erase vR
          If vDB(i, j) &lt;&gt; &quot;&quot; And vDB(1, j) &lt;&gt; &quot;name&quot; And vDB(1, j) &lt;&gt; &quot;COMMENTS&quot; And vDB(1, j) &lt;&gt; &quot;&quot; Then
              n = n + 1
              ReDim Preserve Vr(1 To 5, 1 To n)
              For k = 1 To 2
                  Vr(k, n) = vDB(i, k)
              Next k
              Vr(5, n) = vDB(i, j)
              Vr(3, n) = vDB(1, j)
              Vr(4, n) = wsDesign.Cells(37, j + 1).Interior.Color
          End If
      Next j
          With wsUploadDPK
          Cells.Resize(n, 5) = WorksheetFunction.Transpose(Vr)
          End With
          'ReDim Vr(1 To 5, 1 To n)
           Erase Vr()           
  
  
  Next i
  End With
  </code></pre>