实例需求:工作表中有一个格式化表格(ListObject),现需要将筛选后的表格区域(包含标题行)加载到数组中。
方法1:
Sub Demo1()
Dim oTab As ListObject, visRng As Range
Dim i As Long, j As Long, iR As Long
Dim arr, tmpSht As Worksheet
Set oTab = Sheet1.ListObjects(1)
Set visRng = oTab.Range.SpecialCells(xlCellTypeVisible)
Set tmpSht = Sheets.Add
visRng.Copy tmpSht.Range("A1")
arr = tmpSht.Range("A1").CurrentRegion.Value
Debug.Print UBound(arr), UBound(arr, 2)
Application.DisplayAlerts = False
tmpSht.Delete
Application.DisplayAlerts = True
End Sub
【代码解析】
第6行代码获取ListObject对象。
第7行代码使用SpecialCells(xlCellTypeVisible)
获取筛选后的可见单元格(Range对象),注意筛选可能会产生非连续单元格,后续读取单元格数据时需要特殊处理。
第8行代码创建临时工作表。
第9行代码将筛选后表格拷贝到新建工作表中,Excel将只拷贝可见单元格。
第10行代码将临时工作表中的数据加载到数组中。
第11行代码输入数组的维度。
第12行代码禁止显示错误提示信息。
第13行代码删除临时工作表。
第14行代码恢复错误提示机制。
方法2:
Sub Demo2()
Dim oTab As ListObject, areaRng As Range, visRng As Range
Dim CellCnt As Long, ColCnt As Long, brr(), crr
Set oTab = Sheet1.ListObjects(1)
Set visRng = oTab.Range.SpecialCells(xlCellTypeVisible)
CellCnt = visRng.Cells.Count
ColCnt = visRng.Rows(1).Cells.Count
ReDim brr(1 To CellCnt/ColCnt, 1 To ColCnt)
For Each areaRng In visRng.Areas
crr = areaRng.Value
For i = 1 To UBound(crr)
iR = iR + 1
For j = 1 To UBound(crr, 2)
brr(iR, j) = crr(i, j)
Next
Next
Next
Debug.Print UBound(brr), UBound(brr, 2)
End Sub
【代码解析】
第5行代码获取ListObject对象。
第6行代码使用SpecialCells(xlCellTypeVisible)
获取筛选后的可见单元格(Range对象),注意筛选可能会产生非连续单元格,后续读取单元格数据时需要特殊处理。
第7行代码获取可见单元格的总数量。
第8行代码获取可见表格区域的总列数。
第9行代码为动态数组分配存储空间。
第10~18行代码循环遍历每个Area
(即连续单元格区域),加载至数组中。
第11行代码读取areaRng
单元格区域的内容到数组中。
第12~17行代码将数组crr
中内容转存到brr中。
第19行代码输入数组的维度。