比如原始数据
ID |
11 |
47 |
11 |
48 |
27 |
30 |
43 |
- Sub get_arr()
- Dim arr1(10) 'array必须定义大小后才能使用
- For i = 1 To 10
- arr1(i) = Sheets("sheet3").Cells(i + 1, 1)
- Debug.Print (arr1(i))
- Next i
- End Sub
ID | |||||||||
11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
47 | |||||||||
11 | |||||||||
48 | |||||||||
27 | |||||||||
30 | |||||||||
43 | |||||||||
60 | |||||||||
86 | |||||||||
99 |
- Sub get_arr()
- Dim arr1() 'array必须定义大小后才能使用
- Rem 取第一列的数据试试
- max_row = Sheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row
- ReDim arr1(1 To max_row)
- i = 2
- Do
- i = i + 1
- arr1(i) = Sheets("sheet3").Cells(i, 1)
- Debug.Print (arr1(i))
- Loop While (i + 1) <= max_row
- Sub get_arr()
- Dim arr1() 'array必须定义大小后才能使用
- Rem 取第2行的数据试试
- max_column = Sheets("sheet3").Cells(2, Columns.Count).End(xlToLeft).Column
- ReDim arr1(1 To max_column)
- i = 0
- Do
- i = i + 1
- arr1(i) = Sheets("sheet3").Cells(2, i)
- Debug.Print (arr1(i))
- Loop While (i + 1) <= max_column
max_row = Sheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row
max_row = Sheets("sheet3").Cells(65536, 1).End(xlUp).Row
max_row = Sheets("sheet3").Range("a:a").End(xlDown).Row
max_row = Sheets("sheet3").Range("a:a").End(xlup).Row
max_column = Sheets("sheet3").Cells(2, Columns.Count).End(xlToLeft).Column
max_column = Sheets("sheet3").[iv2].End(xlToLeft).Column
max_column = Sheets("sheet3").Range("iv2").End(xlToLeft).Column
错误的 max_column = Sheets("sheet3").Cells("iv2").End(xlToLeft).Column
max_column = Sheets("sheet3").Range("2:2").End(xlToRight).Column '好像第1列不能是空的
max_column = Sheets("sheet3").Range("2:2").End(xltoleft).Column '好像第1列不能是空的
当前工作表中已使用的列数
ActiveSheet.UsedRange.Columns.Count
https://zhidao.baidu.com/question/1987985423240470147.html
- Sub test1()
- Dim arr1
- Dim arr2
- rem 思路是,先把EXCEL对象读到一个变量里,然后这个变量就是一个二维表对象
- rem 然后把另外一个数组,取这个二维数组的一个维度,取回数据
- rem 重新输入回去也好做吧
- arr1 = Sheets("ganzhi2").UsedRange '这里不能用set arr1 也不能都为数组?只能变量?
- ReDim arr2(1 To UBound(arr1, 2))
- For i = 1 To UBound(arr1, 2) Step 1
- arr2(i) = arr1(1, i)
- Debug.Print arr2(i)
- Next
- For i = 1 To UBound(arr2) Step 1 '这样又写回去了,嗯
- Cells(3, i) = arr2(i)
- Next
- End Sub
http://club.excelhome.net/thread-1250877-1-1.html
需要熟悉 lbound ubound(arr,1)
https://zhidao.baidu.com/question/562477244.html
方法3?
http://club.excelhome.net/forum.php?mod=viewthread&tid=1310859
Sub test1()
Dim vArr
Dim oDic
Set oDic = CreateObject("Scripting.Dictionary")
vArr = Sheets("ganzhi2").UsedRange.Value '数组读表数据
For nRow = 1 To UBound(vArr) '一般第一行是标题,就从第2行开始读
oDic(vArr(nRow, 1)) = nRow '以第一列为字典关键字为例
Next
Debug.Print oDic.Count
End Sub
联系客服