Convert excel named Range to a collection of rows

Question

I currently have a method which takes in a dynamic named range in excel and converts it to a 2D array.

I need to do some iterations to the data and carry out a Delete function if a certain column contains a value. I have looked at the options out there for deleting rows in 2d array using transpose and temp array and since my data is fairly large I am looking at other data structures that would make it easier to delete entire rows.

I want to convert a dynamic named range into a collection in vba. This collection will have a key the row number and as item should have all the data for that row. Basically I would need the ability to iterate through each value in that range like I can do with a 2D array but also the ability to delete a row efficiently and with less hassle than using a 2D array.

Anybody have an idea on how I can achieve this?

Dim srcArray () As Variant
Dim srcRange As Range
srcRange = ThisWorkbook.Worksheets("Main").Range("myNamedRange")
srcArray = srcRange.Value
Dim rowNr As Long
dim colNr As Long

 for rowNr = 1 to UBound(srcArray,1)
         if srcArray(rowNr, 9) = "testString" Then Call DeleteRowSub(srcArray, rowNr)
  Next rowNr

DeleteRowSub will be a sub which will delete a given row based on the index of that row. I want to get away from that and just be able to say something like srcCollection.Remove(index) with index being the row nr.

Any help, greatly appreciated.

look up jagged arrays but a better approach would be to use an autofilter. Plenty of resources around... — , Sep 09 '14 at 10:39

Jean-François Corbett · Accepted Answer · 2014-09-09T10:58:48.420

There's no secret to this. It's just housekeeping.

Function ReadRangeRowsToCollection(r As Range) As Collection
    Dim iRow As Long
    Dim iCol As Long
    Dim rangeArr As Variant
    Dim rowArr As Variant
    Dim c As Collection

    'Read range content to Variant array
    rangeArr = r.Value 

    'Now transfer shit to collection
    Set c = New Collection
    For iRow = 1 To r.Rows.Count
        ReDim rowArr(1 To r.Columns.Count)
        For iCol = 1 To r.Columns.Count
            rowArr(iCol) = rangeArr(iRow, iCol)
        Next iCol
        c.Add rowArr, CStr(iRow)
    Next iRow

    Set ReadRangeRowsToCollection = c
End Function

Example usage:

Dim c As Collection
Set c = ReadRangeRowsToCollection(Range("myNamedRange"))
c.Remove 1 ' remove first row from collection

Note: I haven't looked at edge cases; for example this will fail if the range is one cell only. Up to you to fix it.

Thank you soo much. This is exactly what I needed. You have saved me a good few hours. I was struggling to get my head around the logic. — Jetnor, Sep 09 '14 at 12:15

Convert excel named Range to a collection of rows

1 Answers1