Excel VB 限制单元格下拉拖拽复制 级联清空单元格数据

打开Excel，ALT+F11调出VB窗口

********************************************************

当选择第二列单元格，对应的单元格列清空

********************************************************

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        Cells(Target.Row, "C") = ""
        Cells(Target.Row, "K") = ""
        Cells(Target.Row, "N") = ""
        Cells(Target.Row, "R") = ""
    ElseIf Target.Column = 3 Then
        Cells(Target.Row, "K") = ""
        Cells(Target.Row, "N") = ""
        Cells(Target.Row, "R") = ""
    End If
End Sub

********************************************************

限制单元格(K1-N105)下拉拖拽复制

********************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oCtrl As Office.CommandBarControl
If Not Intersect(Target, Range("$K$1:$N$105")) Is Nothing Then
        Application.CellDragAndDrop = False
  Else
        Application.CellDragAndDrop = True
End If

End Sub

ALT+F11 打开VB窗口,Ctrl+G 在立即窗口里输入 application.celldraganddrop=true  即可解除限制单元格下拉拖拽复制。

注意：VB代码只有在启用宏的情况下实现效果，所以在做以上代码时要启用宏