CODEWITHSUNDEEP
excelvba
Al Grant
Al Grant

Reputation: 2354

Single column values ends up in multi dimension array

I am populating a array with values from part of a column (range). The resulting array is multidimensional - but it should be one dimensional. I want to get just Emp ID values into the array:

enter image description here

I have tried this :

Sub Test()

Dim colPostionNumber As Integer
Dim lastRow As Integer
Dim ws As Worksheet
Dim positionNumberArray As Variant

Set ws = ActiveSheet

With ActiveWorkbook.Sheets("Sheet 1")
    colPositionNumber = Application.WorksheetFunction.Match("Emp ID", ws.Rows(5), 0)
    lastRow = ws.UsedRange.SpecialCells(xlCellTypeLastCell).row

positionNumberArray = .Range(Cells(5, colPositionNumber), Cells(lastRow, colPositionNumber)).Value

End With

End Sub

But the resulting array is two dimensional

enter image description here

I tried reDim but that didn't work. How do I do this with a one dimensional array?

Upvotes: 1

Views: 66

Answers (2)

T.M.
T.M.

Reputation: 9948

Reduce dimension via Excel function ArrayToText()

If you dispose of version MS 365 you could try the following approach via Excel function ArrayToText() and an eventual split action.

Sub reduceDim()
    Dim t#: t = Timer
    Dim rng As Range
    Set rng = Sheet1.Range("B2:B7")            ' << change to your needs
    Dim data
    data = Split(Evaluate("ArrayToText(" & rng.Address(False, False, External:=True) & ")"), ", ")

    Debug.Print "Array(" & LBound(data) & " To " & UBound(data) & ")"
    Debug.Print Join(data, "|")                 ' display resulting 0-based 1-dim array elements

    Debug.Print Format(Timer - t, "0.00 secs")
End Sub

Output in VB Editor's immediate window

    Array(0 To 5)
    1|2|3|4|5|6
    0,00 secs

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54807

Write One-Column 2D Array to 1D Array

To get a zero-based 1D array, you will have to loop.

Sub Test()

    Dim colPositionNumber As Long
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim Data As Variant
    Dim positionNumberArray As Variant
    
    Set ws = ActiveSheet
    
    With ActiveWorkbook.Sheets("Sheet 1")
        colPositionNumber = Application.Match("Emp ID", ws.Rows(5), 0)
        lastRow = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row
        Data = .Range(.Cells(5, colPositionNumber), _
            .Cells(lastRow, colPositionNumber)).Value
        ReDim positionNumberArray(UBound(Data, 1) - 1)
        Dim n As Long
        For n = 1 To UBound(Data, 1)
            positionNumberArray(n - 1) = Data(n, 1)
        Next n
    End With

End Sub

Using Application.Transpose

The following procedures show how to write a one-column or a one-row range to a one-based 1D array:

Sub testATColumn()
    Dim rg As Range: Set rg = Range("A1:A5")
    Dim arr As Variant: arr = Application.Transpose(rg.Value)
    Debug.Print LBound(arr, 1), UBound(arr, 1)
    On Error Resume Next
    Debug.Print LBound(arr, 2), UBound(arr, 2)
    On Error GoTo 0
End Sub

Sub testATRow()
    Dim rg As Range: Set rg = Range("A1:E1")
    Dim arr As Variant
    arr = Application.Transpose(Application.Transpose(rg.Value))
    Debug.Print LBound(arr, 1), UBound(arr, 1)
    On Error Resume Next
    Debug.Print LBound(arr, 2), UBound(arr, 2)
    On Error GoTo 0
End Sub

Note that Application.Transpose has a limit of 65535 elements per dimension.

Upvotes: 1

Related Questions