把不同的范围为二维数组 [英] Putting separate ranges into 2D array

问题描述

我试图让大小的二维数组 [X] [3] 填补。 X 是表（行数）的只是规模和有3列，我很感兴趣，该列不是互相靠近，例如改编[I] [0] 应列AA填补，改编[I] [1] 应该来自K列，和改编[I] [2] 需要从列L;

I'm trying to get a 2D array of size [x][3] filled. X is just the size of the sheet (number of rows) and there are 3 columns which I am interested in. The columns are not near each other, for instance arr[i][0] should be filled from column AA, arr[i][1] should come from column K, and arr[i][2] needs to be from columns L.

我想分配给它下面的方式，但在数组赋值得到了一个错误。

I tried assigning it the following way, but got an error in the array value assignment.

任何帮助将大大AP preciated！

Any help on this would be greatly appreciated!

code：

Sub SOC_work()
'Trying to sort each of the disciplines further, by Stage of Construction

Dim ar_SOC() As Variant
Dim int_NumRows As Long
Dim i_counter As Long
Dim j_Counter As Long
Dim lite As Range

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Sheets("AVEVA_PBOM_PARTS").Select


'Redimension the array size to the amount of parts in the PBOM
int_NumRows = ActiveSheet.UsedRange.Rows.count - 1
ReDim ar_SOC(int_NumRows, 3)

'now assignt he range into the array space
lite = Range("AA2", Range("AA2").End(xlDown))


ar_SOC[][1]=lite



End Sub

有没有办法做到这一点，而无需通过整列循环？

Is there any way to do this without looping through the entire column?

推荐答案

由于在评论中所述，您可以填写三个双维数组。然后，您可以从三个阵列填充第四阵，像下面。

As described in the comments, you can fill three 2-D arrays. You can then populate a fourth array from the three arrays, like below.

Sub populateArray()
    Dim arrColOne() As Variant, arrColTwo() As Variant, arrColThree() As Variant
    Dim arrAllData() As Variant
    Dim i As Long

    arrColOne = Range("A2:A" & lrow(1)) 'amend column number
    arrColTwo = Range("D2:D" & lrow(4))
    arrColThree = Range("G2:G" & lrow(7))

    ReDim arrAllData(1 To UBound(arrColOne, 1), 2) As Variant
    For i = 1 To UBound(arrColOne, 1)
        arrAllData(i, 0) = arrColOne(i, 1)
        arrAllData(i, 1) = arrColTwo(i, 1)
        arrAllData(i, 2) = arrColThree(i, 1)
    Next i
End Sub

Public Function lrow(colNum As Integer) As Long
    lrow = Cells(Rows.Count, colNum).End(xlUp).Row
End Function

以上将要求所有3列是同一长度（否则填充最后一个数组将无法正常工作）;这是由于被redimensioned含有包含在第一阵列中的元素的数量的第四阵列

The above will require all 3 columns to be the same length (otherwise populating the last array will not work); this is due to the fourth array being redimensioned to contain the number of elements contained in the first array.

25万​​行数据测试，第四阵列填充0.43秒。

Testing with 250,000 rows of data, the fourth array populated in 0.43 seconds.

这篇关于把不同的范围为二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！