title: Overwriting & Resizing Arrays in Excel VBA
body: <p>See end for an edit in response to PatricK's comment.</p>
  <p>Apologies if this is a basic question, but I have been looking around here and other sites and cannot find the answer.</p>
  <p>This is my first foray into VBA arrays (I'm new to VBA in general) and I'm struggling with some syntax. I am trying to read some data (part of a single column) from a worksheet into an array, do some processing on it, write it to a different sheet and then overwrite the array with some more data read from the first sheet. One key point is that the number of data points read per iteration will vary (e.g. 4 data points on the first loop, 3 on the second perhaps...) and so the array size should change too.</p>
  <p>My attempt at the code works for the first iteration, but it does not seem to pick up the data on the second loop, instead leaving the array blank. Here's my code (sorry if this doesn't format properly, I only currently have internet access via my phone!):</p>
  <pre><code>Looping...
  
  Dim DataSubSet
  
  With ActiveWorkbook.Worksheets(&quot;Sheet1&quot;).Range(&quot;A&quot; &amp; CStr(I) &amp; &quot;:A&quot; &amp; CStr(io))
  ReDim DataSubSet(1 To .Rows.Count, 1 To .Columns.Count)
  DataSubSet = .Range (&quot;A&quot; &amp; CStr(I) &amp; &quot;:A&quot; &amp; CStr(io))
  End With
  
  **Do processing** 
  
  End loop
  </code></pre>
  <p>Here i and io are variables that determine the cells that I'm interested in; they each change with the overarching loop.</p>
  <p>As I mentioned, this works for the first iteration only. Any pointers on where I've gone wrong would be appreciated!</p>
  <p>Thanks in advance,
  Sam</p>
  <h1>-----------------------------------------------------------------------------------</h1>
  <p>PatricK, for further clarification, here's a quick routine that I knocked up to illustrate your point.</p>
  <p>** formatting check failed (still writing on phone) so wouldn't let me post. Here's a dropbox link to the code in a text file:</p>
  <p><a href="https://db.tt/E0btoI5f" rel="nofollow noreferrer">https://db.tt/E0btoI5f</a></p>