Excel VBA Appending data to single Array

I think you are asking for more information about moving data between ranges and variables so that is the question I will attempt to answer.

Create a new workbook. Leave Sheet1 empty; set cell B3 of Sheet2 to "abc" and set cells C4 to F6 of Sheet3 to ="R"&ROW()&"C"&COLUMN()

Open the VB Editor, create a module and copy the follow code to it. Run macro Demo01().

Option Explicit
Sub Demo01()

  Dim ColURV As Long
  Dim InxWkSht As Long
  Dim RowURV As Long
  Dim UsedRangeValue As Variant

  ' For each worksheet in the workbook
  For InxWkSht = 1 To Worksheets.Count
    With Worksheets(InxWkSht)
      Debug.Print .Name
      If .UsedRange Is Nothing Then
        Debug.Print "  Empty sheet"
      Else
        Debug.Print "  Row range: " & .UsedRange.Row & " to " & _
                            .UsedRange.Row + .UsedRange.Rows.Count - 1
        Debug.Print "  Col range: " & .UsedRange.Column & " to " & _
                            .UsedRange.Column + .UsedRange.Columns.Count - 1
      End If
      UsedRangeValue = .UsedRange.Value
      If IsEmpty(UsedRangeValue) Then
        Debug.Print "  Empty sheet"
      ElseIf VarType(UsedRangeValue) > vbArray Then
        ' More than one cell used
        Debug.Print "  Values:"
        For RowURV = 1 To UBound(UsedRangeValue, 1)
          Debug.Print "  ";
          For ColURV = 1 To UBound(UsedRangeValue, 2)
            Debug.Print "  " & UsedRangeValue(RowURV, ColURV);
          Next
          Debug.Print
        Next
      Else
        ' Must be single cell worksheet
        Debug.Print "  Value = " & UsedRangeValue
      End If

    End With
  Next

End Sub

The following will appear in the Immediate Window:

Sheet1
  Row range: 1 to 1
  Col range: 1 to 1
  Empty sheet
Sheet2
  Row range: 3 to 3
  Col range: 2 to 2
  Value = abc
Sheet3
  Row range: 4 to 6
  Col range: 3 to 5
  Values:
    R4C3  R4C4  R4C5
    R5C3  R5C4  R5C5
    R6C3  R6C4  R6C5

If you work through the macro and study the output you will get an introduction to loading a range to a variant. The points I particularly want you to note are:

• The variable to which the range is loaded is of type Variant. I have never tried loading a single range to a Variant array since the result may not be an array. Even if it works, I would find this confusing.
• Sheet1 is empty but the used range tells you than cell A1 is used. However, the variant to which I have loaded the sheet is empty.
• The variant only becomes an array if the range contains more than one cell. Note: the array will ALWAYS be two dimensional even if the range is a single row or a single column.
• The lower bounds of the array are ALWAYS 1.
• The column and row dimensions are not standard with the rows as dimension 1 and the columns as dimension 2.
• If there is any doubt about the nature of the range being loaded, you must use IsEmpty and VarType to test its nature.

You may also like to look at: https://stackoverflow.com/a/16607070/973283. Skim the explanations of macros Demo01() and Demo02() which are not relevant to you but set the context. Macro Demo03() shows the advanced technique of loading multiple worksheets to a jagged array.

Now create a new worksheet and leave it with the default name of Sheet4.

Add the follow code to the module. Run macro Demo02().

Sub Demo02()

  Dim ColOut As Long
  Dim OutputValue() As String
  Dim Rng As Range
  Dim RowOut As Long
  Dim Stg As String

  ReDim OutputValue(5 To 10, 3 To 6)

  For RowOut = LBound(OutputValue, 1) To UBound(OutputValue, 1)
    For ColOut = LBound(OutputValue, 2) To UBound(OutputValue, 2)
      OutputValue(RowOut, ColOut) = RowOut + ColOut
    Next
  Next

  With Worksheets("Sheet4")
    Set Rng = .Range("A1:D6")
  End With
  Rng.Value = OutputValue

  With Worksheets("Sheet4")
    Set Rng = .Range(.Cells(8, 2), .Cells(12, 4))
  End With
  Rng.Value = OutputValue

  With Worksheets("Sheet4")
    Stg = "C" & 14 & ":G" & 20
    Set Rng = .Range(Stg)
  End With
  Rng.Value = OutputValue

End Sub

Although this macro writes an array to a worksheet, many of the points apply for the opposite direction. The points I particularly want you to note are:

• For output, the array does not have to be Variant nor do the lower bounds have to be 1. I have made OutputValue a String array so the values output are strings. Change OutputValue to a Variant array and rerun the macro to see the effect.
• I have used three different ways of creating the range to demonstrate some of your choices.
• If you specify a range as I have, the worksheet is one of the properties of the range. That is why I can take Rng.Value = OutputValue outside the With ... End With and still have the data written to the correct worksheet.
• When copying from a range to a variant, Excel sets the dimensions of the variant as appropriate. When copying from an array to a range, it is your responsibility to get the size of the range correct. With the second range, I lost data. With the third range, I gained N/As.

I hope the above gives you an idea of your options. If I understand your requirement correctly, you will have to:

• Load the entire worksheet to Variant
• Create a new Array of the appropriate size
• Selectively copy data from the Variant to the Array.

Come back withh questions if anything is unclear.