使用ReDim preserve在Excel中使用VBA多维数组 [英] ReDim Preserve with multidimensional array in Excel VBA

问题描述

我能得到这个工作，但我不知道这是否是正确的或这样做的最有效的方式。

详细信息：通过151行，那么分配列循环 A 和 B 只有那些行到一个二维数组基于列的标准 C 。与标准只有151行的114需要在阵列中

我知道，与使用ReDim preserve你只能调整最后的数组维，你可以不会改变的维数。所以我在数组中大小的行要使用 LRow 变量总151行，但实际行我只需要在数组中的变量 ValidRow 如此看来，（151-114）= 37多余行是数组使用ReDim preserve行的结果英寸我想使数组只有大，因为它必须是114行不是151，但不知道这是否可能见code以下，pciated，因为我是新来的阵列和任何帮助非常AP $ P $花了两天看着这最精彩的部分。注：列是一个常数没有问题，他们却各不相同的行

 子FillArray2（）昏暗数据（）为Variant
暗淡ValidRow，R，LRow作为整数表（Contract_BR_CONMaster）。选择
LRow =范围（A1）。完（xlDown）.Row'151总行擦除数据（）当r = 2至LRow
 如果细胞（R，3）.value的＆LT;＆GT; 大桥和单元格（R，3）.value的＆LT;＆GT; 桥要然后
  ValidRow = ValidRow + 1
  使用ReDim preserve数据（1 LRow，1〜2）
  数据（ValidRow，1）=范围（A＆安培; R）.value的填充阵列柱A
  数据（ValidRow，2）=范围（B＆放大器; r）的。价值'填充阵列列B
 万一接下来řActiveWorkbook.Worksheets（测试）范围（A2：B＆放大器; ValidRow + 1）=数据（）循环后分配已经走过所有的数据运行，并评估它结束小组
 

解决方案

还有两个这样的方法。
FillArray4 - 初始阵列中创建的code这个动作太大，但第二部分采用双循环，来创建数组是它需要的确切大小的新数组

 子FillArray4（）昏暗数据（）为Variant，数据2（）为Variant
昏暗ValidRow作为整数，R为整数，lRow作为整数表（Contract_BR_CONMaster）。选择
lRow =范围（A1）。完（xlDown）.Row'151总行第I部分 - 阵列大于它必须是
擦除数据（）当r = 2至lRow
 如果细胞（R，3）.value的＆LT;＆GT; 大桥和单元格（R，3）.value的＆LT;＆GT; 桥要然后
  ValidRow = ValidRow + 1'这是数组必须是114行的大小
  使用ReDim preserve数据（1 lRow，1〜2），但使阵列基于lrow不ValidRow为使用preserve时不能动态调整数组的第一个昏暗的是151行
  数据（ValidRow，1）=范围（A＆安培; R）.value的填充阵列柱A
  数据（ValidRow，2）=范围（B＆放大器; r）的。价值'填充阵列列B
 万一
接下来ř第二部分
从数据（）数组，它是太大，新的数组数据2（移动数据）的大小非常，因为它使用ValidRow代替lrow的
擦除数据2（）对于i = LBOUND（数据，1）UBound函数（数据，1）'行
对于J = LBOUND（数据，2）UBound函数（数据，2）COLS
 如果不为IsEmpty（数据（I，J））。然后
  使用ReDim preserve数据2（1至ValidRow，1〜2）
  数据2（I，J）=数据（I，J）您与原始阵列数据，但只有一个非空变暗新数组罢了;数据2（I，J）不是一个特定行或山坳其在阵列中的交叉点
  相对于第一部分，你填补了最初的阵列使用单独的线对每个山坳从COLS A和B的数据
 万一下一个
下一个
ActiveWorkbook.Worksheets（测试）范围（A2：B＆放大器; ValidRow + 1）=数据2（）'新阵列的数据分配到工作表结束小组
 

子FillArray5 - 更简单，我的preferred选项因为你只创建一个数组。初始循环确定的大小的阵列需要，然后第二环路使用该创建阵列和存储数据。注意：只有两个在两种情况下COLS。问题我在这种情况下被创建的二维数组，其中行变化。这对我来说时间去热带地区的不好赚了假期！

 子FillArray5（）昏暗数据（）为Variant
昏暗ValidRow作为整数，R为整数，lRow整数，DimCount整数，RemSpaceInArr作为整数表（Contract_BR_CONMaster）。选择
lRow =范围（A1）。完（xlDown）.Row擦除数据（）当r = 2至lRow
 如果细胞（R，3）.value的＆LT;＆GT; 大桥和单元格（R，3）.value的＆LT;＆GT; 桥要然后
  ValidRow = ValidRow + 1'这是数组必须是114行的大小
 万一
接下来řDimCount = 0'复位
 当r = 2至lRow
  如果细胞（R，3）.value的＆LT;＆GT; 大桥和单元格（R，3）.value的＆LT;＆GT; 桥要然后
   使用ReDim preserve数据（1 ValidRow，1〜2）使得使用ValidRow从上述第一循环数组的确切大小114行
   DimCount = DimCount + 1'需要这个，否则ValidRow开始朦胧在114，但需要和从1开始增量ValidRow的最大值
   数据（DimCount，1）=范围（A＆安培; R）.value的填充阵列柱A
   数据（DimCount，2）=范围（B＆放大器; r）的。价值'填充阵列列B
  万一
 接下来ř
 RemSpaceInArr = ValidRow  -  DimCount'只是一个检查应该是0ActiveWorkbook.Worksheets（测试）范围（A2：B＆放大器; ValidRow + 1）=数据（）'从数组分配数据到工作表结束小组
 

I can get this to work but am not sure if this is the correct or the most efficient way of doing this.

Details: Looping through 151 rows then assigning column A and B only of those rows to a two dimensional array based on criteria in column C. With the criteria only 114 of the 151 rows are needed in the array.

I know that with ReDim Preserve you can only resize the last array dimension and you can't change the number of dimensions at all. So I have sized the rows in the array to be the total 151 rows using the LRow variable but the actual rows I only need in the array is in variable ValidRow so it seems that (151-114) = 37 superfluous rows are in the array as a result of the ReDim Preserve line. I would like to make the array only as big as it needs to be which is 114 rows not 151 but not sure if this is possible see code below and any help much appreciated as I am new to arrays and have spent the best part of two days looking at this. Note: Columns are a constant no issue with them but rows vary.

Sub FillArray2()

Dim Data() As Variant
Dim ValidRow, r, LRow As Integer

Sheets("Contract_BR_CONMaster").Select
LRow = Range("A1").End(xlDown).Row '151 total rows

Erase Data()

For r = 2 To LRow
 If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
  ValidRow = ValidRow + 1
  ReDim Preserve Data(1 To LRow, 1 To 2)
  Data(ValidRow, 1) = Range("A" & r).Value 'fills the array with col A
  Data(ValidRow, 2) = Range("B" & r).Value 'fills the array with col B
 End If

Next r

ActiveWorkbook.Worksheets("Test").Range("A2:B" & ValidRow + 1) = Data() 'assign after     loop has run through all data and assessed it

End Sub

解决方案

Two more ways of doing this. FillArray4 - Initial array is created too large but second part of code moves this to a new array using a double loop which creates the array to be the exact size it needs to be.

Sub FillArray4()

Dim Data() As Variant, Data2() As Variant
Dim ValidRow As Integer, r As Integer, lRow As Integer

Sheets("Contract_BR_CONMaster").Select
lRow = Range("A1").End(xlDown).Row '151 total rows

'Part I - array is bigger than it has to be
Erase Data()

For r = 2 To lRow
 If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
  ValidRow = ValidRow + 1 'this is the size the array needs to be 114 rows
  ReDim Preserve Data(1 To lRow, 1 To 2) 'but makes array to be 151 rows as based on lrow not ValidRow as cannot dynamically resize 1st dim of array when using preserve
  Data(ValidRow, 1) = Range("A" & r).Value 'fills the array with col A
  Data(ValidRow, 2) = Range("B" & r).Value 'fills the array with col B
 End If
Next r

'Part II
'move data from Data() array that is too big to new array Data2() that is perfectly sized as it uses ValidRow instead of lrow
Erase Data2()

For i = LBound(Data, 1) To UBound(Data, 1) 'Rows
For j = LBound(Data, 2) To UBound(Data, 2) 'Cols
 If Not IsEmpty(Data(i, j)) Then
  ReDim Preserve Data2(1 To ValidRow, 1 To 2)
  Data2(i, j) = Data(i, j) 'fills the new array with data from original array but only non blank dims; Data2(i,j) is not one particular row or col its an intersection in the array
  'as opposed to part one where you fill the initial array with data from cols A and B using seperate lines for each col
 End If

Next
Next
ActiveWorkbook.Worksheets("Test").Range("A2:B" & ValidRow + 1) = Data2() 'assign data from new array to worksheet

End Sub

Sub FillArray5 - Much simpler and my preferred option as you only create one array. Initial loop determines the size the array needs to be and then second loop uses this to create array and store data. Note only two cols in both cases. Issue I had in this scenario was creating 2D array where rows varied. That's it for me time to go to the tropics for a well earned holiday!

Sub FillArray5()

Dim Data() As Variant
Dim ValidRow As Integer, r As Integer, lRow As Integer, DimCount As Integer,  RemSpaceInArr As Integer

Sheets("Contract_BR_CONMaster").Select
lRow = Range("A1").End(xlDown).Row

Erase Data()

For r = 2 To lRow
 If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
  ValidRow = ValidRow + 1 'this is the size the array needs to be 114 rows
 End If
Next r

DimCount = 0 'reset
 For r = 2 To lRow
  If Cells(r, 3).Value <> "Bridge From" And Cells(r, 3).Value <> "Bridge To" Then
   ReDim Preserve Data(1 To ValidRow, 1 To 2) 'makes array exact size 114 rows using ValidRow from first loop above
   DimCount = DimCount + 1 'need this otherwise ValidRow starts the dim at 114 but needs to start at 1 and increment to max of ValidRow
   Data(DimCount, 1) = Range("A" & r).Value 'fills the array with col A
   Data(DimCount, 2) = Range("B" & r).Value 'fills the array with col B
  End If
 Next r
 RemSpaceInArr = ValidRow - DimCount 'just a check it should be 0

ActiveWorkbook.Worksheets("Test").Range("A2:B" & ValidRow + 1) = Data() 'assign data from array to worksheet

End Sub

这篇关于使用ReDim preserve在Excel中使用VBA多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！