vba 将名为 Range 的 excel 转换为行的集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25742291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert excel named Range to a collection of rows
提问by Jetnor
I currently have a method which takes in a dynamic named range in excel and converts it to a 2D array.
我目前有一种方法可以在 excel 中接收动态命名范围并将其转换为二维数组。
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.
我想将动态命名范围转换为 vba 中的集合。该集合将有一个键行号，并且作为项目应该包含该行的所有数据。基本上，我需要能够像使用 2D 数组一样遍历该范围内的每个值，而且还需要能够有效地删除一行，并且比使用 2D 数组更省事。
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.
DeleteRowSub 将是一个子项，它将根据该行的索引删除给定的行。我想摆脱这种情况，只能说 srcCollection.Remove(index) 之类的东西，索引是行 nr。
Any help, greatly appreciated.
任何帮助，不胜感激。
回答by Jean-Fran?ois Corbett
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.
注意：我没有看过边缘情况；例如，如果范围只有一个单元格，这将失败。由你来修复它。
