这节的例子很实用,值得好好学习。最重要的一点,原来复制单元格竟然可以用赋值语句来完成,简直是神操作啊。
Option Explicit
'1 单元格输入
Sub t1()
Range("a1") = "a" & "b"
Range("b1") = "a" & Chr(10) & "b" '换行答输入
End Sub
'2 单元格复制和剪切
Sub t2()
Range("a1:a10").Copy Range("c1") 'A1:A10的内容复制到C1
End Sub
Sub t3()
Range("a1:a10").Copy
ActiveSheet.Paste Range("d1") '粘贴至D1
End Sub
Sub t4()
Range("a1:a10").Copy
Range("e1").PasteSpecial (xlPasteValues) '只粘贴为数值
End Sub
Sub t5()
Range("a1:a10").Cut
ActiveSheet.Paste Range("f1") '粘贴到f1
End Sub
Sub t6()
Range("c1:c10").Copy
Range("a1:a10").PasteSpecial Operation:=xlAdd '选择粘贴-加
End Sub
Sub T7()
Range("G1:G10") = Range("A1:A10").Value
End Sub
'3 填充公式
Sub T8()
Range("b1") = "=a1*10"
Range("b1:b10").FillDown '向下填充公式
End Sub
Option Explicit
Sub c1()
Rows(4).Insert
End Sub
Sub c2() '插入行并复制公式
Rows(4).Insert
Range("3:4").FillDown
Range("4:4").SpecialCells(xlCellTypeConstants) = ""
End Sub
Sub c3()
Dim x As Integer
For x = 2 To 20
If Cells(x, 3) <> Cells(x + 1, 3) Then
Rows(x + 1).Insert
x = x + 1
End If
Next x
End Sub
Sub c4()
Dim x As Integer, m1 As Integer, m2 As Integer
Dim k As Integer
m1 = 2
For x = 2 To 1000
If Cells(x, 1) = "" Then Exit Sub
If Cells(x, 3) <> Cells(x + 1, 3) Then
m2 = x
Rows(x + 1).Insert
Cells(x + 1, "c") = Cells(x, "c") & " 小计"
Cells(x + 1, "h") = "=sum(h" & m1 & ":h" & m2 & ")"
Cells(x + 1, "h").Resize(1, 4).FillRight
Cells(x + 1, "i") = ""
x = x + 1
m1 = m2 + 2
End If
Next x
End Sub
Sub c44()
'个人方法
Dim x As Integer
Dim t As Integer
t = Range("c65536").End(xlUp).Row
For x = t To 2 Step -1
If Cells(x, 3) <> Cells(x - 1, 3) Then
Rows(x).Insert
Cells(Cells(x, "C").Offset(1, 0).End(xlDown).Row + 1, "C") = Cells(Cells(x, "C").Offset(1, 0).End(xlDown).Row, "C") & " 小计"
Cells(Cells(x, "H").Offset(1, 0).End(xlDown).Row + 1, "H") = _
Application.Sum(Range(Cells(x, "h").Offset(1, 0), Cells(x, "H").Offset(1, 0).End(xlDown)))
End If
Next x
End Sub
Sub dd() '删除小计行
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub