title: Excel VBA For each sheet get contents of a row and put in an array
body: <p>I have a current project that requires me to open an excel doc, figure out what format the doc is in (what the "headers" are as A:A contents), then figure out if it adheres to a new document layout format, and convert necessary data to conform to the new format. </p>
  
  <p>I have to do this for thousands of files, so I want to automate as much of the discovery portion as possible, and plan to only need to write a few one-off solutions for some content types (docs with specific data which needs to be preserved as an exception to the new formatting). </p>
  
  <p>That's the context for what I'm trying to do. I've gotten to the point of trying to populate an array of the contents of row A:A in each sheet, but my ReDim statement is failing as I guarantee I'm doing something wrong with it. </p>
  
  <p>Here's what I've got from the point where I get into the sheet: </p>
  
  <pre><code>For Each Sht In SourceWorkbook.Sheets
      Sht.Activate
  SourceHeaderCount = ActiveSheet.UsedRange.Columns.Count
  Set SourceHeaderRange = Sht.Range("A1:A" &amp; SourceHeaderCount)
  ReDim Preserve SourceHeaders(0 To UBound(SourceHeaders) + SourceHeaderCount)
  SourceHeaders(UBound(SourceHeaders)) = SourceHeaderRange
  Next Sht
  </code></pre>
  
  <p>Again, the idea is to grab the contents of A:A (used contents only), so that I have a list of what the file does have, then compare that list with what I need it to have, then from there I need to create sub functions to handle each type of conversion. First thing's first though, I need to know what the file has. Any ideas how I can make this work? I recognize that this approach has flaws when confronting non-standardized data (like what if a2 is unused but a3 is used?) but for the purposes of this context, assume all documents are easy to work with and there's no real strange data scheme to worry about. </p>
  
  <p>Thanks for the help!</p>