UsedRange和Dict

1、 字典案例：

Sub sameSum()
Dim i, j As Integer
Dim arr()

Dim d, k, t

'创建字典对象
Set d = CreateObject("Scripting.Dictionary")

'最后一行数据
n = [a65536].End(xlUp).Row

arr = Range("a2:b" & n)

'字典有个特性，d("张三")，如果字典里有关键字张三，就出来张三对应的值，没有的话，就创建一个张三的关键字，对应空值
For i = 1 To UBound(arr)
    d(arr(i, 1)) = d(arr(i, 1)) + arr(i, 2) '利用字典关键字不重复性，将相同项目加总，PS:如求重复次数用1
Next

k = d.keys  '导出字典的关键字
t = d.items '导出字典的值

Columns("e:f").Clear

[e2].Resize(d.Count, 1) = Application.Transpose(k)  '将关键字写入表二的A列
[f2].Resize(d.Count, 1) = Application.Transpose(t)  '将加总值写入表二的B列
[e1].Resize(1, 2) = Array("字段", "求和") '做表头

Erase k, t, arr
Set d = Nothing


End Sub

2、Resize简单使用

Sub copy_data()
'Range("h1").Resize(3, 5).Merge
Dim x As String
Dim k As Integer

x = Application.InputBox("请输入部门", "选择部门", Type:=2)

'复制标题
[a1:c1].Copy [h1]
k = 1

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = x Then
        k = k + 1
        Cells(i, 1).Resize(1, 3).Copy Cells(k, "h") '将加总值写入表二的B列
    End If
Next

End Sub

3、UsedRange使用

Sub ss()
[c1] = ActiveSheet.UsedRange.Rows.Count
[c2] = ActiveSheet.UsedRange.Columns.Count

'在正常的使用时；
Sub ss()
[c1] = ActiveSheet.UsedRange.Rows.Count
[c2] = ActiveSheet.UsedRange.Columns.Count

Dim cellRange As Range, RowNum As Long, ColNum As Long
Set cellRange = Worksheets("sheet1").UsedRange '设置已用单元格区域并赋值给变量
RowNum = cellRange.Rows.Count   '已用单元格区域的行数
ColNum = cellRange.Columns.Count    '已用单元格区域的列数

End Sub