Excel VB limit cell drop-down copy cascade clear cell data, excel Cell

Source: Internet
Author: User

Excel VB limit cell drop-down copy cascade clear cell data, excel Cell

Open Excel, ALT + F11 bring up VB window

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

When the second column of cells is selected, the corresponding cell column is cleared.

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

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



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

Limit cell (K1-N105) drop-down drag-and-drop Replication

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


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 open the VB window, Ctrl + G enter application. celldraganddrop = true in the immediate window to remove the restriction cell drop-down and drag-and-drop copy.

Note: The VB Code only achieves the effect when macros are enabled. Therefore, enable macros when the preceding code is used.


You cannot drag or drop a formula in an EXCEL worksheet to show only the data of the previously copied cells.

Tool-Option-re-calculate, automatically re-calculate and select, OK.

In an excel table, how does one drag the handle? By default, the drop-down operation is to copy cells, rather than arrange the cells in sequence. If you do not right-click the handle, just pull it down.

For numeric data, dragging is equivalent to copying. For date data and text data, dragging is equivalent to pressing ctrl to drag the fill sequence. Therefore, pressing ctrl to drag down is to copy ctrl z and hold down

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.