none
How to select a contiguous range in Excel? RRS feed

  • Question

  • Hello,

    I have this line in my VBA (which works fine):

    vArray_2D = Worksheets("Database").Range("A1:A20").Value

    which copies the range A1:A20 into a 2-dimensional array.

    -----

    My next step is to fill column A of the "Database" worksheet with any length of contiguous values starting from A1.

    (This column of variable size would be filled by SQL)

    -----

    Then I want to use this range to fill my array:

    I am trying this:

    Selection = Range ("A1", Range ("A1").End (xlDown)).Select

    vArray_2D = Worksheets("Database").Selection.Value

    -----

    But it does not seem to work.

    Any help will be much appreciated.

    Thanks

    Leon


    • Edited by Leon Lai Friday, November 15, 2019 10:08 AM
    Friday, November 15, 2019 10:07 AM

Answers

  • Hi, Peter Thornton

    Thanks for your reply.

    The solution I wanted is simply this:

    ----

    Dim vArray_2D As Variant
    vArray_2D = Worksheets("Database1").Range("A1", Range("A1").End(xlDown))

    ----

    Unfortunately, my program has crashed somewhere, and this is why my above code did not work.

    Now, I have to write my whole program over again because it's frozen completely.

    Best Regards,

    Leon

    • Marked as answer by Leon Lai Saturday, November 16, 2019 5:13 AM
    • Unmarked as answer by Leon Lai Saturday, November 16, 2019 5:13 AM
    • Marked as answer by Leon Lai Saturday, November 16, 2019 11:40 PM
    Friday, November 15, 2019 3:14 PM

All replies

  • Adapt what you need from the following

    Sub test()
    Dim lastRow As Long
    Dim vArray_2D(), arrSQL()
    Dim rng1 As Range, rng2 As Range
    
        Set rng1 = ActiveWorkbook.Worksheets("Database").Range("A1:A20")
        vArray_2D = rng1.Value
    
        rng1.ClearContents
        rng1.Offset(, 2).ClearContents  ' only for testing
    
        ReDim arrSQL(1 To rng1.Rows.Count, 1 To 1)
        For i = 1 To UBound(arrSQL)
            If i <> 10 Then
                arrSQL(i, 1) = i * 10
            End If
        Next
    
        rng1(1, 1).Resize(UBound(arrSQL), 1).Value = arrSQL
        rng1.Value = arrSQL
        If rng1(1, 1) = "" Then
            ' A1 is empty
        Else
            lastRow = rng1(1, 1).End(xlDown).Row
            Set rng2 = rng1(1, 1).Resize(lastRow - rng1(1, 1).Row + 1, 1)
    Debug.Print rng2.Address     ' only for testing
            vArray_2D = rng2.Value
    
            rng2.Offset(, 2).Value = vArray_2D
        End If
    
    End Sub

    Friday, November 15, 2019 10:52 AM
    Moderator
  • Hi, Peter Thornton

    Thanks for your reply.

    The solution I wanted is simply this:

    ----

    Dim vArray_2D As Variant
    vArray_2D = Worksheets("Database1").Range("A1", Range("A1").End(xlDown))

    ----

    Unfortunately, my program has crashed somewhere, and this is why my above code did not work.

    Now, I have to write my whole program over again because it's frozen completely.

    Best Regards,

    Leon

    • Marked as answer by Leon Lai Saturday, November 16, 2019 5:13 AM
    • Unmarked as answer by Leon Lai Saturday, November 16, 2019 5:13 AM
    • Marked as answer by Leon Lai Saturday, November 16, 2019 11:40 PM
    Friday, November 15, 2019 3:14 PM