他山之石——使用VBA完成单元格高级操作（复制、粘贴、分列汇总）（Advanced range operation with VBA）

这节的例子很实用，值得好好学习。最重要的一点，原来复制单元格竟然可以用赋值语句来完成，简直是神操作啊。

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