CODEWITHSUNDEEP
excelvba
SmIqbal
SmIqbal

Reputation: 99

Populate a two dimensional array with a single loop

I have tried below code to fill a two dimensional array in Excel VBA and I was able to get the desired results. I would like to know if there is a better way of doing this or if you foresee any technical issue once I have a significantly large size of data in real case situations. Any ideas or suggestions would be appreciated for improvement.

Sub test_selection()
' My below array is based on values contained within
' selected cells
' The purpose of using two dimensional array is to 
' keep values in one column of array 
' while retaining cell addresses in 2nd 
' dimension to print some info in relevant cells
' offset to the selected cells

Dim anArray() As String

firstRow = Selection.Range("A1").Row
LastRow = Selection.Rows(Selection.Rows.Count).Row
colum = Selection.Columns.Column
arrSize = LastRow - firstRow

ReDim anArray(0 To arrSize, 1)
cnt = 0

For i = firstRow To LastRow
    anArray(cnt, 0) = CStr(Cells(i, colum).Value2)
    anArray(cnt, 1) = Cells(i, colum).Address
    cnt = cnt + 1
Next i

Call TestGetFileList(anArray)

End Sub

Upvotes: 0

Views: 78

Answers (1)

user11174618
user11174618

Reputation:

When you have a significantly large size of data, that loop through the worksheet is going to be slow. Probably better to grab all of the data at once and reprocess it in memory.

Option Explicit

Sub test_selection()
' My below array is based on values contained within
' selected cells
' The purpose of using two dimensional array is to
' keep values in one column of array
' while retaining cell addresses in 2nd
' dimension to print some info in relevant cells
' offset to the selected cells

    Dim i As Long, r As Long, c As String, anArray As Variant

    With Selection
        c = Split(.Cells(1).Address, "$")(1)
        r = Split(.Cells(1).Address, "$")(2) - 1
        anArray = .Columns(1).Cells.Resize(.Rows.Count, 2).Value2
    End With

    For i = LBound(anArray, 1) To UBound(anArray, 1)
        anArray(i, 1) = CStr(anArray(i, 1))
        anArray(i, 2) = "$" & c & "$" & i + r
    Next i

    TestGetFileList anArray

End Sub

Upvotes: 1

Related Questions