arrays - 在Excel VBA中制作一个由来自不同工作表的多个范围值组成的数组
问题描述
我正在尝试创建一个值数组,这些值是从数据电子表格具有的 x 多个工作表中获得的。
目前这是我到目前为止所拥有的
Sub Test()
Workbooks.Open("dataex.xlsx").Activate
Dim i, x, y, z, sheet_num
Dim allsheets As Variant
Dim sheet As Variant
Dim sheets As Variant '
Dim list As Variant
Dim ws As Worksheet
i = Application.sheets.Count
x = 1
ReDim allsheets(1 To i)
For Each ws In Worksheets
allsheets(x) = ws.Name
x = x + 1
Next ws
sheets = allsheets
For Each sheet In sheets
tmp = Range("A2").CurrentRegion.Value
y = Range("A1").CurrentRegion.Rows.Count
z = Range("A1").CurrentRegion.Columns.Count
list = Range(Cells(1, 1), Cells(y, z))
Next sheet
End Sub
我附上了一张图片来显示我创建的假数据(为简单起见,每张纸上的数据相同)
最后,我想得到一个名为list
z 列数相同的数组,但值的行将被添加到彼此的下方,然后调整数组的大小并添加它来自的工作表。
解决方案
我以前做过类似的事情,它看起来像这样:
Sub Test()
Dim i As Long, wb As Workbook, data(), numSheets As Long
Dim rng As Range, numCol As Long, totRows As Long, allData()
Dim rw As Long, col As Long, arr, r As Long, firstSheet As Boolean
Set wb = Workbooks.Open("dataex.xlsx")
numSheets = wb.Worksheets.Count
ReDim data(1 To numSheets)
firstSheet = True 'controls whether we skip the header row
'loop over the sheets and collect the data
For i = 1 To numSheets
Set rng = wb.Worksheets(i).Range("A1").CurrentRegion
'ignore empty sheets
If Application.CountA(rng) > 0 Then
'remove the header if not first sheet
If Not firstSheet Then Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
data(i) = rng.Value 'collect the data
totRows = totRows + UBound(data(i), 1) 'add the row count
firstSheet = False 'done one sheet
End If
Next i
'size the final output array
ReDim allData(1 To totRows, 1 To UBound(data(1), 1))
r = 1
'combine the array from each sheet into the final array
For i = 1 To numSheets
If Not IsEmpty(data(i)) Then 'sheet had data?
arr = data(i)
For rw = 1 To UBound(arr, 1)
For col = 1 To UBound(arr, 2)
allData(r, col) = arr(rw, col)
Next col
r = r + 1
Next rw
End If
Next i
'add a new sheet and dump the array
With wb.sheets.Add(after:=wb.sheets(wb.sheets.Count))
.Range("A1").Resize(totRows, UBound(allData, 2)).Value = allData
End With
End Sub
推荐阅读
- amazon-web-services - 如何将 SSL 证书上传到 IAM
- javascript - NodeJs MVC 架构,这是通心粉代码吗?
- zk - 为什么/何时 Executions.getCurrent() 为空?- 如何调试它?
- angular - 如何从具有特定分钟间隔的特定小时开始创建小时数组
- javascript - 忽略 JSON 属性并仅获取值
- reactjs - 函数缺少返回类型 - 在反应(打字稿)代码中
- python - 如何将 zodb 对象日期时间转换为 Python?
- c# - C# Google Analytics api 调用错误的 SSIS 执行进程任务说操作系统的应用程序无效
- python - 当您具有相同的值时如何拆分列?
- java - 'BoxLayout 不能与 .add 共享'