The following methods provide you with three different ways of reading a range into a collection in VBA.
Source Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
Public Function readRangeIntoTableCollection(ByRef rng As Range, ByVal keyCol As Integer) As Scripting.Dictionary Dim i As Long Dim j As Integer Dim rec As Scripting.Dictionary Dim dict As New Scripting.Dictionary If Not (rng Is Nothing) Then If keyCol <> 0 Then ' Loop through all rows in a given range object. For i = 2 To rng.Rows.Count Set rec = New Scripting.Dictionary ' Loop through all columns in a given range object. For j = 1 To rng.Columns.Count ' Read one record. rec.Add rng.Cells(1, j).value, rng.Cells(i, j).value Next j ' Add one record to returned collection. dict.Add rng.Cells(i, keyCol).value, rec Next i End If End If Set readRangeIntoTableCollection = dict End Function ' @Author - Alexander Bolte ' @ChangeDate - 2014-10-09 ' @Description - Reading a provided Range objects values into a collection holding key value pairs. ' @Param rng - a Range object holding one or more columns. ' @Param keyCol - a column within provided Range holding keys. ' @Param valCol - a column within provided Range holding values. ' @Remarks - The index for columns starts in a provided Range, not in the worksheet. ' If for example the handed Range starts in column C of a worksheet ' and you need the first column of handed Range as keys, you would provide 1 and not 3 as keyCol. Public Function readRangeIntoCollection(ByRef rng As Range, ByVal keyCol As Integer, ByVal valCol As Integer) As Scripting.Dictionary Dim i As Long Dim dict As New Scripting.Dictionary If Not (rng Is Nothing) Then If keyCol <> 0 And valCol <> 0 Then For i = 1 To rng.Rows.Count dict.Add rng.Cells(i, keyCol).value, rng.Cells(i, valCol).value Next i End If End If Set readRangeIntoCollection = dict End Function ' @Author - Alexander Bolte ' @ChangeDate - 2014-10-09 ' @Description - Reading a provided Range objects values into a collection. ' @Param rng - a Range object holding one column. ' @Returns a collection of type VBA.Collection holding all values ' from the first column in provided Range object. Public Function readRangeIntoVbaCollection(ByRef rng As Range) As VBA.Collection Dim i As Long Dim dict As New VBA.Collection If Not (rng Is Nothing) Then For i = 1 To rng.Rows.Count If rng.Rows(i).Hidden = False Then dict.Add Trim(rng.Cells(i, 1).value) End If Next i End If Set readRangeIntoVbaCollection = dict End Function
