1 表格的三种表示方法
Sheets("1月").Select
Sheets(2).Select '该种方式较常用,可较表量sheets(i)
Sheet1.Select
2 复制表
Sheet3.Copy after:=Sheets(Sheets.Count) '复制表,加在最后一张表后面
3 对应弹窗和程序运行屏幕显示的处理
Excel.Application.DisplayAlerts = FALSE '忽略弹窗
Application.ScreenUpdating = False ' 忽略程序运行在屏幕上的闪烁
For i = 1 To 100
Sheets(1).Delete
Next
Excel.Application.DisplayAlerts = True '要成对出现,恢复正常
Application.ScreenUpdating = true '要成对出现,恢复正常
4 处理文件并保持文档
Application.ScreenUpdating = False
Excel.Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\Users\ttxs\Desktop\入职学习\跟着王佩丰学VBA附件\跟着王佩丰学VBA附件\VBA04\test.xlsx" '打开文档
ActiveWorkbook.Sheets(1).Range("c4") = 1
ActiveWorkbook.Save '保存
ActiveWorkbook.Close
Excel.Application.DisplayAlerts = True
Application.ScreenUpdating = True
5 拆分表格分别保存
Dim sht As Worksheet '定义变量为表格
Application.ScreenUpdating = False
For Each sht In Sheets '遍历所有表
sht.Copy '整张表复制保存
ActiveWorkbook.SaveAs Filename:="C:\Users\ttxs\Desktop\入职学习\data\" & sht.Name & ".xlsx"
ActiveWorkbook.Close
Next
Application.ScreenUpdating = True
5 删除空白行,要从后往前删除
Dim sht As Worksheet
Dim i As Integer
For Each sht In Sheets
For i = sht.UsedRange.Rows.Count To 2 Step -1 '倒着删,假如正删,删除一行空白行,下一行会往上整,导致遗漏
'性别
If sht.Range("e" & i) = "男" Then
sht.Range("f" & i) = "先生"
Else
sht.Range("f" & i) = "女士"
End If
'科目
If sht.Range("b" & i) = "理工" Then
sht.Range("c" & i) = "lg"
ElseIf sht.Range("b" & i) = "文科" Then
sht.Range("c" & i) = "wk"
Else
sht.Range("c" & i) = "cj"
End If
'空白
If sht.Range("d" & i) = "" Then
sht.Select '选定表
sht.Range("d" & i).Select '选定单元格
sht.Range("D" & i).EntireRow.Delete '整行删除
End If
Next
Next
6 操作单元格常用方法
'单元格表示
[a10]
cells(10,1)
range("a10")
range("a10").value
'单元格位置变形
range("a1").offset(10,0) '定位,在循环中巧用
Range("a65535").End (xlUp).row '找一个表的最后一行
'单元格操作
range("a10").EntireRow '整行
range("a10").resize(1,10) ' 扩展
range("a10").resize(1,10).merge ' 合并单元格
range("a10").copy '复制
7 for i 和 for each 的使用
Dim i, j, k As Integer
For i = 2 To Sheet1.Range("a65536").End(xlUp).Row '找最后一行
For j = 2 To Sheets.Count '遍历表格
If Sheet1.Range("d" & i) = Sheets(j).Name Then
k = Sheets(j).Range("a65536").End(xlUp).Row + 1 '找最后一行的下一行,复制到这里
Sheet1.Range("d" & i).EntireRow.Copy Sheets(j).Range("a" & k)
End If
Next
Next
分析:上述循环为什么不用 for each,因为each一般对表格或者单元格,循环后不好定位出单元格所占的行,进行整行复制