利用Excel粘贴板读取可见区域单元格数据到2维数组

催微不带悲伤

1. Function RangeToArray(rng As Range)
   
2.     Dim MyData As New DataObject
   
3.     Dim rmg, rmg2, x, y
   
4.     rng.Copy
   
5.     MyData.GetFromClipboard
   
6.     rmg = MyData.GetText
   
7.     Application.CutCopyMode = False
   
8.    
   
9.     rmg2 = rmg
   
10.     x = Len(rmg) - Len(Replace(rmg2, vbCrLf, "_"))
    
11.     y = UBound(Split(Split(rmg, vbCrLf)(0), vbTab)) + 1
    
12.     Dim Newarray
    
13.     ReDim Newarray(1 To x, 1 To y)
    
14.    
    
15.     For i = 1 To x
    
16.         a1 = Split(rmg, vbCrLf)(i - 1)
    
17.         For j = 1 To y
    
18.             Newarray(i, j) = Split(a1, vbTab)(j - 1)
    
19.         Next
    
20.     Next
    
21.     RangeToArray = Newarray
    
22. End Function

复制代码

1. sub 测试()
   
2. dim sn
   
3.     sn = RangeToArray(Selection.SpecialCells(xlCellTypeVisible))
   
4. end sub

复制代码