基于當前活動行的動態選擇更改-有解無憂

我為自己創建了一個包含多個相關下拉串列的作業表。基本上，我希望在范圍內發生更改時清除范圍。因此，我撰寫了下面的代碼，它按預期作業，但遠非整潔。

這是代碼：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("D13").Address Then
Range("D13:H13").Value = ""
ElseIf Target.Address = Range("D14").Address Then
Range("D14:H14").Value = ""
ElseIf Target.Address = Range("D15").Address Then
Range("D15:H15").Value = ""
ElseIf Target.Address = Range("D16").Address Then
Range("D16:H16").Value = ""
ElseIf Target.Address = Range("D17").Address Then
Range("D17:H17").Value = ""
ElseIf Target.Address = Range("D18").Address Then
Range("D18:H18").Value = ""
ElseIf Target.Address = Range("D19").Address Then
Range("D19:H19").Value = ""
ElseIf Target.Address = Range("D20").Address Then
Range("D20:H20").Value = ""
ElseIf Target.Address = Range("D21").Address Then
Range("D21:H21").Value = ""
ElseIf Target.Address = Range("D22").Address Then
Range("D22:H22").Value = ""
ElseIf Target.Address = Range("D23").Address Then
Range("D23:H23").Value = ""
ElseIf Target.Address = Range("D24").Address Then
Range("D24:H24").Value = ""
ElseIf Target.Address = Range("D25").Address Then
Range("D25:H25").Value = ""
ElseIf Target.Address = Range("D26").Address Then
Range("D26:H26").Value = ""
ElseIf Target.Address = Range("D27").Address Then
Range("D27:H27").Value = ""
ElseIf Target.Address = Range("D28").Address Then
Range("D28:H28").Value = ""
ElseIf Target.Address = Range("D29").Address Then
Range("D29:H29").Value = ""
End If
End Sub

現在我想實作的是這樣的：

If Target.Address = Range("D13:D29").Address Then
Range("D Target.Address.Row : H Target.Address.Row").Value = ""
End If
End Sub

如果有人能讓我在如何在范圍選擇或更合適的解決方案/選項中使用 Target.Address 的正確方向我會非常感謝:)

uj5u.com熱心網友回復：

您可以使用回圈，Resize因此您不必一遍又一遍地重復相同的代碼：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim AffectedRange As Range  'check wich target cells are affected
    Set AffectedRange = Intersect(Target, Me.Range("D13:D29"))

    If Not AffectedRange Is Nothing Then
        Dim Cell As Range
        For Each Cell in AffectedRange ' clear range for each affected cell
            Cell.Resize(ColumnSize:=5).Value = vbNullString
        Next Cell
    End If
End Sub

uj5u.com熱心網友回復：

代碼可以簡化為這個。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("D13:D29")) Is Nothing Then
        Target.Resize(, 5).Value = ""
    End If
   
End Sub

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/331470.html

標籤：擅长 vba excel-公式

上一篇：對組進行排序并保留空間

下一篇：根據excel上一列中的行值創建一列