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... — user2140173
– user2140173, Commented Sep 9, 2014 at 10:39

Jean-François Corbett · Accepted Answer · 2014-09-09 10:58:48Z

4

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.

edited Sep 9, 2014 at 10:58

answered Sep 9, 2014 at 10:53

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jetnor Over a year ago

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.

Collectives™ on Stack Overflow

Convert excel named Range to a collection of rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related