How to properly assign an array range not starting from an index of 1 to an excel range?

Question

When I do the code shown below, the array automatically re-dimensions itself to Z(1 to 10) and I do not like how it re-dimensions because I always want to keep the array positions the same as its row positions in the spreadsheet. But I will still be able to assign the array to the excel range by equating both to each other as shown below:

Dim range_test As Range
Set range_test = ThisWorkbook.Worksheets("Sheet1").Range("A2:A11")

ReDim Z(2 To 11) As Variant
Z = range_test
ThisWorkbook.Worksheets("Sheet1").Range("D2:D11") = Z

But when I include the entire range (including the first row or the header, then my issue is that I cannot use the code I used above to assign the array to the excel range else it will include the header. So I will have to do it the way shown below:

Dim range_test As Range
Set range_test = ThisWorkbook.Worksheets("Sheet1").Range("A1:A11")

ReDim Z(1 To 11) As Variant
Z = range_test

Dim i As Long
For i = 2 To 11
    ThisWorkbook.Worksheets("Sheet1").Range("E" & i) = Z(i, 1)
Next i

What I want to do is something similar to the logic where the arrays allow you to slice it similarly to other languages such as MATLAB because I feel creating a loop for populating an array is more resource heavy compared to equating an array to a range and vice versa (please correct me if i'm wrong). Please refer to the conceptualization below:

ThisWorkbook.Worksheets("Sheet1").Range("G2:G11") = Z(2 to 11)
ThisWorkbook.Worksheets("Sheet1").Range("H2:H11") = Y(2 to 11)

Answer 1

If I understand you correctly, you can avoid a loop thus

ThisWorkbook.Worksheets("Sheet1").Range("E2:E11").Resize(range_test.Count - 1) = Application.Index(Z, Evaluate("row(2:10)"))

You don't need to hard-code the "2:10" bit.

Answer 2

Array LBound Greater Than 1

Headers, Offset, Resize

• Examples 1 and 2 refer to Range("A2:A11").
• Examples 3 and 4 respectively refer to Range("A1:A11").

The Code

Option Explicit

Sub Array1()
    Dim range_test As Range
    Set range_test = ThisWorkbook.Worksheets("Sheet1").Range("A2:A11")

    ReDim Z(2 To 11) As Variant
    'ReDim Z(range_test.Row, range_test.Row + range_test.Rows.Count - 1)
    Z = range_test
    ThisWorkbook.Worksheets("Sheet1").Range("D2:D11") = Z
End Sub

Sub Array2()
    Dim range_test As Range
    Dim Z As Variant
    Set range_test = ThisWorkbook.Worksheets("Sheet1").Range("A2:A11")

    Z = range_test
    ThisWorkbook.Worksheets("Sheet1").Range("D2:D11") = Z
End Sub

Sub Array3()
    Const HeaderRows As Long = 1
    Dim range_test As Range
    Set range_test = ThisWorkbook.Worksheets("Sheet1").Range("A1:A11")

    ReDim Z(HeaderRows + 1 To 11) As Variant
    'ReDim Z(HeaderRows + range_test.Row, _
            range_test.Row + range_test.Rows.Count - 1)
    Z = range_test.Offset(HeaderRows).Resize(range_test.Rows.Count - HeaderRows)
    ThisWorkbook.Worksheets("Sheet1").Range("D2:D11") = Z
End Sub

Sub Array4()
    Const HeaderRows As Long = 1
    Dim range_test As Range
    Dim Z As Variant
    Set range_test = ThisWorkbook.Worksheets("Sheet1").Range("A1:A11")

    Z = range_test.Offset(HeaderRows).Resize(range_test.Rows.Count - HeaderRows)
    ThisWorkbook.Worksheets("Sheet1").Range("D2:D11") = Z
End Sub

Conclusion Step by Step

Sub Array5()

    Const cSheet As Variant = "Sheet1"  ' Worksheet Name/Index
    Const cHeaders As Long = 1          ' Number of Header Rows
    Const cSource As String = "A1:A11"  ' Initial Range Address
    Const cTarget As Variant = "D"      ' Target Column Letter/Number

    Dim rngInit As Range      ' Initial Range
    Dim rngSource As Range    ' Source Range
    Dim rngFirst As Range     ' First Cell Range of Target Range
    Dim rngTarget As Range    ' Target Range
    Dim vntSource As Variant  ' Source Array

    With ThisWorkbook.Worksheets(cSheet)
        ' Create a reference to Initial Range.
        Set rngInit = .Range(cSource)
        With rngInit
            ' Calculate Source Range by manipulating Initial Range.
            Set rngSource = .Offset(cHeaders).Resize(.Rows.Count - cHeaders)
        End With
        ' Copy Source Range to Source Array.
        vntSource = rngSource
        ' Calculate First Cell Range of Target Range.
        Set rngFirst = .Cells(rngInit.Row + cHeaders, cTarget)
        ' Adjust size of Target Range to size of Source Array.
        Set rngTarget = rngFirst.Resize(UBound(vntSource))
        ' Write Source Array to Target Range.
        rngTarget = vntSource
    End With

End Sub

Final Conclusion (No Object References)

Sub Array6()

    Const cSheet As Variant = "Sheet1"  ' Worksheet Name/Index
    Const cHeaders As Long = 1          ' Number of Header Rows
    Const cSource As String = "A1:A11"  ' Initial Range Address
    Const cTarget As Variant = "D"      ' Target Column Letter/Number

    Dim vntSource As Variant  ' Source Array

    With ThisWorkbook.Worksheets(cSheet).Range(cSource)
        ' Calculate Source Range by manipulating Initial Range.
        ' Copy Source Range to Source Array.
        vntSource = .Offset(cHeaders).Resize(.Rows.Count - cHeaders)
        ' Calculate First Cell Range of Target Range.
        ' Adjust size of Target Range to size of Source Array.
        ' Write Source Array to Target Range.
        .Parent.Cells(.Row + cHeaders, cTarget) _
               .Resize(UBound(vntSource)) = vntSource
    End With

End Sub

Remarks

I had no idea that you can:

• Redim without previously Dim under Option Explicit,
• Dim a 1D array and copy a range to it 'transforming' it into a 2D array.