利用Excel粘贴板读取可见区域单元格数据到2维数组
Function RangeToArray(rng As Range)
Dim MyData As New DataObject
Dim rmg, rmg2, x, y
rng.Copy
MyData.GetFromClipboard
rmg = MyData.GetText
Application.CutCopyMode = False
rmg2 = rmg
x = Len(rmg) - Len(Replace(rmg2, vbCrLf, "_"))
y = UBound(Split(Split(rmg, vbCrLf)(0), vbTab)) + 1
Dim Newarray
ReDim Newarray(1 To x, 1 To y)
For i = 1 To x
a1 = Split(rmg, vbCrLf)(i - 1)
For j = 1 To y
Newarray(i, j) = Split(a1, vbTab)(j - 1)
Next
Next
RangeToArray = Newarray
End Function
sub 测试()
dim sn
sn = RangeToArray(Selection.SpecialCells(xlCellTypeVisible))
end sub
页:
[1]