1、 字典案例：

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

Dim d, k, t

'創(chuàng)建字典對象
Set d = CreateObject("Scripting.Dictionary")

'最后一行數(shù)據(jù)
n = [a65536].End(xlUp).Row

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

'字典有個特性，d("張三")，如果字典里有關(guān)鍵字張三，就出來張三對應(yīng)的值，沒有的話，就創(chuàng)建一個張三的關(guān)鍵字，對應(yīng)空值
For i = 1 To UBound(arr)
    d(arr(i, 1)) = d(arr(i, 1)) + arr(i, 2) '利用字典關(guān)鍵字不重復(fù)性，將相同項目加總，PS:如求重復(fù)次數(shù)用1
Next

k = d.keys  '導(dǎo)出字典的關(guān)鍵字
t = d.items '導(dǎo)出字典的值

Columns("e:f").Clear

[e2].Resize(d.Count, 1) = Application.Transpose(k)  '將關(guān)鍵字寫入表二的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)

'復(fù)制標(biāo)題
[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 '設(shè)置已用單元格區(qū)域并賦值給變量
RowNum = cellRange.Rows.Count   '已用單元格區(qū)域的行數(shù)
ColNum = cellRange.Columns.Count    '已用單元格區(qū)域的列數(shù)

End Sub