Excel Macro Entire Column into 1-D Array

Question

I have a data sheet like this

ID    Name
-------------------
B23     Max
D27     Nads
W34     sads
A65     Robin
C37     Harvard
C65     Nivkai
V87     adsdasd
Q78     sadsad

I need to put all IDs into an "One dimetional Array" as string.So I tried this,

Dim RowCount As Integer
RowNumber = wb1.Sheets(1).UsedRange.Rows.Count

Dim idArray() As String
For j = 1 To RowNumber
    ID = wb1.Sheets(1).Cells(j, 1).Value
    ReDim idArray(j)
    idArray(j) = CStr(ID)
Next j

My main goal is to apply a filter on a different sheet using this idArray in this way

wb2.Sheets(1).Range(Selection, Selection.End(xlDown)).AutoFilter Field:=1, Criteria1:=idArray(), Operator:=xlFilterValues

But later when I'm trying to print the entire array using below code, its printing nothing.And seems idArray() is empty.

For n = 1 To UBound(idArray)
    Debug.Print QidArray(n)
Next n

Can anyone please tell me what I'm doing wrong.

Thanks,

SWa · Accepted Answer · 2013-09-10 11:18:11Z

Whilst Mehow has eloquently answered your specific request, on a more general note, try to avoid looping through the worksheet - it's slow. It's always faster to copy the array in memory and loop through that - here are a few alternatives:

1. Unique Values Only

Sub UniqueValuesOnly()

Dim vData, idArray  As Variant
Dim x               As Long
Dim oDic            As Object

Set oDic = CreateObject("scripting.dictionary")

vData = Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 1).Value

For x = LBound(vData) + 1 To UBound(vData)
    If Not oDic.exists(vData(x, 1)) Then
        oDic.Add vData(x, 1), Nothing
    End If
Next x

idArray = oDic.keys


End Sub

2. No looping for smaller amounts of data

Sub AllSmallData()

Dim idArray

With Sheets(1).Cells(1, 1).CurrentRegion
    idArray = Application.Transpose(.Offset(1).Resize(.Rows.Count - 1, 1).Value)
End With

End Sub

3. Looping through array for a large amount of data

Sub AllLargeData()
Dim idArray() As String, vData
Dim x As Long

With Sheets(1).Cells(1, 1).CurrentRegion
    vData = .Offset(1).Resize(.Rows.Count - 1, 1).Value
End With

ReDim idArray(1 To UBound(vData))

For x = LBound(vData) To UBound(vData)
    idArray(x) = vData(x, 1)
Next x

End Sub

score 3 · Accepted Answer · 2013-09-10 13:03:21Z

You can specify the size of an array at the creation time - you know how many items you will be entering into your 1D array » the RowNumber variable (the amount of cells in a specific column). You use ReDim instead of Dim to be able to change the size of array at runtime. In this particular case it doesn't really suit you as it doesn't seem you will be removing duplicate from array, etc. you want to use a dynamic number of rows only. It's worth knowing why you will be using ReDim though:)

ReDim idArray(RowNumber) as String

You don't need a special ID variable as you can use one loop to fill the array.

ReDim idArray(RowNumber) As String
Dim j As Long
For j = 1 To RowNumber
    idArray(j - 1) = CStr(wb1.Sheets(1).Cells(j, 1).Value)
Next j
ReDim Preserve idArray(UBound(idArray) - 1)

The ReDim Preserve idArray(UBound(idArray) - 1) decreases the size (length) of the array by 1 as the index starts at 0 and rows 1. So first item in your array starts at 0, which is equal to lbound(arr) in this case. If somewhere in the code you redimed your array to arr(5 to 10) then the lbound() would be 5 not 0.

When iterating over array you use two bound functions wchich return numerical representation of the array bounds lbound() and ubound(). This will print your entire array to the Immediate Window CTRL+G

For j = LBound(idArray) To UBound(idArray)
    Debug.Print idArray(j)
Next j

So, the solution here could look like this:

Sub Arrr()

    Dim wb1 As Workbook
    Set wb1 = ThisWorkbook

    Dim RowNumber As Long
    RowNumber = wb1.Sheets(1).UsedRange.Rows.Count

    ReDim idArray(RowNumber) As String
    Dim j As Long
    For j = 1 To RowNumber
        idArray(j - 1) = CStr(wb1.Sheets(1).Cells(j, 1).Value)
    Next j

    ReDim Preserve idArray(UBound(idArray) - 1)
    For j = LBound(idArray) To UBound(idArray)
        Debug.Print j, idArray(j)
    Next j

End Sub

Collectives™ on Stack Overflow

Excel Macro Entire Column into 1-D Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related