title: Excel VBA - How to Redim a 2D array?
body: <p>In Excel via Visual Basic, I am iterating through a CSV file of invoices that is loaded into Excel. The invoices are in a determinable pattern by client.</p>
  
  <p>I am reading them into a dynamic 2D array, then writing them to another worksheet with older invoices. I understand that I have to reverse rows and columns since only the last dimension of an array may be Redimmed, then transpose when I write it to the master worksheet.</p>
  
  <p>Somewhere, I have the syntax wrong. It keeps telling me that I have already Dimensionalized the array. Somehow did I create it as a static array? What do I need to fix in order to let it operate dynamically?</p>
  
  <p><strong>WORKING CODE PER ANSWER GIVEN</strong></p>
  
  <pre><code>Sub InvoicesUpdate()
  '
  'Application Settings
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.Calculation = xlCalculationManual
  
  'Instantiate control variables
  Dim allRows As Long, currentOffset As Long, invoiceActive As Boolean, mAllRows As Long
  Dim iAllRows As Long, unusedRow As Long, row As Long, mWSExists As Boolean, newmAllRows As Long
  
  'Instantiate invoice variables
  Dim accountNum As String, custName As String, vinNum As String, caseNum As String, statusField As String
  Dim invDate As String, makeField As String, feeDesc As String, amountField As String, invNum As String
  
  'Instantiate Workbook variables
  Dim mWB As Workbook 'master
  Dim iWB As Workbook 'import
  
  'Instantiate Worksheet variables
  Dim mWS As Worksheet
  Dim iWS As Worksheet
  
  'Instantiate Range variables
  Dim iData As Range
  
  'Initialize variables
  invoiceActive = False
  row = 0
  
  'Open import workbook
  Workbooks.Open ("path:excel_invoices.csv")
  Set iWB = ActiveWorkbook
  Set iWS = iWB.Sheets("excel_invoices.csv")
  iWS.Activate
  Range("A1").Select
  iAllRows = iWS.UsedRange.Rows.Count 'Count rows of import data
  
  'Instantiate array, include extra column for client name
  Dim invoices()
  ReDim invoices(10, 0) 
  
  'Loop through rows.
  Do
  
      'Check for the start of a client and store client name
      If ActiveCell.Value = "Account Number" Then
  
          clientName = ActiveCell.Offset(-1, 6).Value
  
      End If
  
      If ActiveCell.Offset(0, 3).Value &lt;&gt; Empty And ActiveCell.Value &lt;&gt; "Account Number" And ActiveCell.Offset(2, 0) = Empty Then
  
          invoiceActive = True
  
          'Populate account information.
          accountNum = ActiveCell.Offset(0, 0).Value
          vinNum = ActiveCell.Offset(0, 1).Value
          'leave out customer name for FDCPA reasons
          caseNum = ActiveCell.Offset(0, 3).Value
          statusField = ActiveCell.Offset(0, 4).Value
          invDate = ActiveCell.Offset(0, 5).Value
          makeField = ActiveCell.Offset(0, 6).Value
  
      End If
  
      If invoiceActive = True And ActiveCell.Value = Empty And ActiveCell.Offset(0, 6).Value = Empty And ActiveCell.Offset(0, 9).Value = Empty Then
  
          'Make sure something other than $0 was invoiced
          If ActiveCell.Offset(0, 8).Value &lt;&gt; 0 Then
  
              'Populate individual item values.
              feeDesc = ActiveCell.Offset(0, 7).Value
              amountField = ActiveCell.Offset(0, 8).Value
              invNum = ActiveCell.Offset(0, 10).Value
  
              'Transfer data to array
              invoices(0, row) = "=TODAY()"
              invoices(1, row) = accountNum
              invoices(2, row) = clientName
              invoices(3, row) = vinNum
              invoices(4, row) = caseNum
              invoices(5, row) = statusField
              invoices(6, row) = invDate
              invoices(7, row) = makeField
              invoices(8, row) = feeDesc
              invoices(9, row) = amountField
              invoices(10, row) = invNum
  
              'Increment row counter for array
              row = row + 1
  
              'Resize array for next entry
              ReDim Preserve invoices(10,row)
  
           End If
  
      End If
  
      'Find the end of an invoice
      If invoiceActive = True And ActiveCell.Offset(0, 9) &lt;&gt; Empty Then
  
          'Set the flag to outside of an invoice
          invoiceActive = False
  
      End If
  
      'Increment active cell to next cell down
      ActiveCell.Offset(1, 0).Activate
  
  'Define end of the loop at the last used row
  Loop Until ActiveCell.row = iAllRows
  
  'Close import data file
  iWB.Close
  </code></pre>