vba 单元格选取

daijinzhou 2016-06-30

展开全文

1、选取一个单元格：
   Range("A1").select
   Range("A" & 1).select
   Cells(1).select
   Cells(1, 1).select
   Cells(1, "A").select
2、选取连续单元格：
   Range("a1:b10").select
   Range("a1", "b10").select
   Range(Cells(1, 1), Cells(10, 2)).select
3、选取不连续单元格：
   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、实现分块合并单元格
   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

3. Sub 提取数据()

5. '原表“库存单.xls”不是很规范，现在需要把原表的数据提取整理到“汇总.xls”中

6. '原表数据很多，这里只截取了部分表格及代码

8. Dim DataWorkbook As Workbook '库存单.xls工作簿

9. Dim DataSheet As Worksheet, DataSheetName As String 'DataSheet=当前操作的工作表，DataSheetName=当前操作工作表的表名

10. Dim HuizongSheet As Worksheet '汇总单工作表

11. Dim Goods As Range, GoodCount As Long

12. Dim GoodTime As String '进货时间

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 '产品计数归零

25.

26. For Each DataSheet In DataWorkbook.Sheets '遍历所有库存单工作表

27.

28. '遍历每个库存单工作表的“总数”列第二行到最后一个非空行，好像这里不能取到正确的值

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

30. If IsNumeric(Goods.Value) Then '当“总数”单元格为数值时，判定为找到一个产品（不以产品名称为基准的原因是：有的产品没有产品名称）

31. GoodCount = GoodCount + 1 '产品数加1

32.

33. '获取库存单中的产品名称，填充到汇总单的A列

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

35.

36. '获取库存单中的该产品的所有进货时间，填充到汇总单的B列

37. GoodTime = "进货时间："

38. For i = Goods.Row To Goods.Offset(1, 0).Row - 1 '以合并单元格Goods所占的行区域为基准循环

39. GoodTime = GoodTime & " " & DataSheet.Range("H" & i).Value '循环获取进货时间

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.Select
Call 设置A1格式
Next
End Sub
--------------------------------------
2.Sub 遍历工作表()
For Each sh In Worksheets '数组
if sh.name <> 表名1 and sh.name <>表名 2 then
         sh.Select
         Call 设置A1格式
end if
Next
End Sub
--------------------------------------
3.Sub 循环工作表()
For Each sh In Worksheets
      If sh.Index > 2 Then '限定工作表范围
   sh.Select
            Call 设置A1格式
      End If
Next
End Sub
--------------------------------------
4.Sub 遍历工作表()
For Each sh In Worksheets '数组
If sh.Name Like "*" & "表" & "*" Then    '如果工作表名称包含“表”
      sh.Select
      Call 设置A1格式
End If
Next
End Sub
5.Sub 遍历工作表()
For Each sh In Worksheets '数组
If Not sh.Name Like "*" & "表" & "*" Then    '如果工作表名称不包含“表”
      sh.Select
      Call 设置A1格式
End If
Next
End Sub
6.Sub 遍历工作表()
For Each sh In Worksheets '数组
If sh.Name <> "价格表" And sh.Name <> "人员表" Then '指定不参与循环的工作表名称，可一个或多个，自行增减
      sh.Select
      Call 设置A1格式
End If
Next
End Sub