1、選取一個單元格：
   Range("A1").select
   Range("A" & 1).select
   Cells(1).select
   Cells(1, 1).select
   Cells(1, "A").select
2、選取連續(xù)單元格：
   Range("a1:b10").select
   Range("a1", "b10").select
   Range(Cells(1, 1), Cells(10, 2)).select
3、選取不連續(xù)單元格：
   Range("a1,b2,c3").select
   Union(Range("a1"), Range("b2"), Range("c3")).select
   Union(Cells(1, 1), Cells(2, 2), Cells(3, 3)).select
4、選取行：
   Range("1:1").select
   Rows("1:1").select
   Rows(1).select
   Range("2:10").select
   Rows("2:10").select
   Range("1:1,3:5").select
5、選取列：
   Range("A:A").select
   Columns("A:A").select
   Columns(1).select
   Range("A:F").select
   Columns("A:F").select
   Range("A:B,D:F").select
6、選取所有單元格：
   Cells.select
7、實(shí)現(xiàn)分塊合并單元格
   Range("b1:g1, h1:m1, n1:s1").MergeCells = True
**************************************************
取最后一行行號：i = Range("A65536").End(xlUp).Row
取最后一列列號：m = Range("dz1").End(xlToLeft).Column
**************************************************

Nlr=Activesheet.Cells.SpecialCells(xlLastCell).Row '最后一行
nLC = Activesheet.Cells.SpecialCells(xlLastCell).Column '最后一列

Sub tt()
For i = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(i).Cells(1, 1).Value = "a"
Next
End Sub

Sub aaaa()Dim sh1, sh2 As WorksheetDim shName, cellValue As String'On Error Resume Next

Set sh1 = Workbooks(1).Sheets(1)'Workbooks.Open Filename:="D:\_jack\Finance Report\Report Layout\TA Opex Report 2014.xlsx"

Workbooks.Open Filename:="D:\_jack\Finance Report\Report Layout\Rolling PL Template v1 (2).xlsx"

For i = 1 To Workbooks(2).Worksheets.Count

    Workbooks(2).Activate

    Set sh2 = ActiveWorkbook.Worksheets(i)

    sh2.Activate

    n = sh2.Name

    'v = sh2.Cells(5, 7).Value  'G5單元格

    v = sh2.Cells(4, 3).Value  'C4單元格    

    Workbooks(1).Activate

    sh1.Activate

    sh1.Cells(i, 1) = n

    sh1.Cells(i, 2) = v

    Next

Workbooks(2).CloseEnd Sub

1.   Option Explicit

2.    

3.   Sub 提取數(shù)據(jù)()

4.    

5.   '原表“庫存單.xls”不是很規(guī)范，現(xiàn)在需要把原表的數(shù)據(jù)提取整理到“匯總.xls”中

6.   '原表數(shù)據(jù)很多，這里只截取了部分表格及代碼

7.    

8.   Dim DataWorkbook As Workbook '庫存單.xls工作簿

9.   Dim DataSheet As Worksheet, DataSheetName As String 'DataSheet=當(dāng)前操作的工作表，DataSheetName=當(dāng)前操作工作表的表名

10. Dim HuizongSheet As Worksheet '匯總單工作表

11. Dim Goods As Range, GoodCount As Long

12. Dim GoodTime As String '進(jìn)貨時間

13. Dim i As Long

14.  

15. On Error Resume Next

16. Set DataWorkbook = Workbooks("庫存單.xls")

17. If Err <> 0 Then

18.     MsgBox "庫存單.xls 文件沒有打開！", vbExclamation

19.     Exit Sub

20. End If

21.  

22. Set HuizongSheet = Worksheets("匯總單")

23.  

24. GoodCount = 0 '產(chǎn)品計(jì)數(shù)歸零

25.  

26. For Each DataSheet In DataWorkbook.Sheets '遍歷所有庫存單工作表

27.  

28.     '遍歷每個庫存單工作表的“總數(shù)”列第二行到最后一個非空行，好像這里不能取到正確的值

29.     For Each Goods In DataSheet.Range([F2], Cells(Rows.Count, "F").End(xlUp)).Cells

30.         If IsNumeric(Goods.Value) Then '當(dāng)“總數(shù)”單元格為數(shù)值時，判定為找到一個產(chǎn)品（不以產(chǎn)品名稱為基準(zhǔn)的原因是：有的產(chǎn)品沒有產(chǎn)品名稱）

31.             GoodCount = GoodCount + 1 '產(chǎn)品數(shù)加1

32.             

33.             '獲取庫存單中的產(chǎn)品名稱，填充到匯總單的A列

34.             HuizongSheet.Range("A" & GoodCount).Value = DataSheet.Name & " - " & DataSheet.Range("C" & Goods.Row).Value

35.             

36.             '獲取庫存單中的該產(chǎn)品的所有進(jìn)貨時間，填充到匯總單的B列

37.             GoodTime = "進(jìn)貨時間："

38.             For i = Goods.Row To Goods.Offset(1, 0).Row - 1 '以合并單元格Goods所占的行區(qū)域?yàn)榛鶞?zhǔn)循環(huán)

39.                 GoodTime = GoodTime & "  " & DataSheet.Range("H" & i).Value '循環(huán)獲取進(jìn)貨時間

40.             Next i

41.             HuizongSheet.Range("B" & Goods).Value = GoodTime

42.             

43.         End If

44.     Next Goods

45. Next DataSheet

46.  

47. End Sub

.Sub 遍歷工作表()
For Each sh In Worksheets    '數(shù)組
    sh.Select
    Call 設(shè)置A1格式
Next
End Sub
--------------------------------------
2.Sub 遍歷工作表()
For Each sh In Worksheets    '數(shù)組
    if sh.name <> 表名1 and sh.name <>表名 2 then
            sh.Select
            Call 設(shè)置A1格式
    end if 
Next
End Sub
--------------------------------------
3.Sub 循環(huán)工作表()
    For Each sh In Worksheets
        If sh.Index > 2 Then    '限定工作表范圍
       sh.Select
              Call 設(shè)置A1格式
        End If
    Next
End Sub
--------------------------------------
4.Sub 遍歷工作表()
For Each sh In Worksheets    '數(shù)組
    If sh.Name Like "*" & "表" & "*" Then     '如果工作表名稱包含“表”
        sh.Select
        Call 設(shè)置A1格式
    End If
Next
End Sub
5.Sub 遍歷工作表()
For Each sh In Worksheets    '數(shù)組
    If Not sh.Name Like "*" & "表" & "*" Then     '如果工作表名稱不包含“表”
        sh.Select
        Call 設(shè)置A1格式
    End If
Next
End Sub
6.Sub 遍歷工作表()
For Each sh In Worksheets    '數(shù)組
    If sh.Name <> "價格表" And sh.Name <> "人員表" Then    '指定不參與循環(huán)的工作表名稱，可一個或多個，自行增減
        sh.Select
        Call 設(shè)置A1格式
    End If
Next
End Sub

Sub aaaa()

Dim sh1, sh2 As Worksheet

Dim shName, cellValue As String

'On Error Resume Next

Set sh1 = Workbooks(1).Sheets(1)

'Workbooks.Open Filename:="D:\_jack\Finance Report\Report Layout\TA Opex Report 2014.xlsx"

Workbooks.Open Filename:="D:\_jack\Finance Report\Report Layout\Rolling PL Template v1 (2).xlsx"

For i = 1 To Workbooks(2).Worksheets.Count

    Workbooks(2).Activate

    Set sh2 = ActiveWorkbook.Worksheets(i)

    sh2.Activate

    n = sh2.Name

    'v = sh2.Cells(5, 7).Value  'G5單元格

    v = sh2.Cells(4, 3).Value  'C4單元格

    Workbooks(1).Activate

    sh1.Activate

    sh1.Cells(i, 1) = n

    sh1.Cells(i, 2) = v

Next

Workbooks(2).Close

End Sub

Sub 激活單元格區(qū)域()

    Dim rgfirst As Range

    Dim rglast As Range

    Dim s As String

    Dim s1 As String

    Dim s2 As String

    On Error Resume Next

    s = Application.InputBox("請輸入要查詢的內(nèi)容：")

    s1 = Split(s, ",")(0)

    s2 = Split(s, ",")(1)

    Set rgfirst = ActiveSheet.UsedRange.Find(s1)

    Set rglast = ActiveSheet.UsedRange.Find(s2)

    Range(rgfirst, rglast).Activate

End Sub