根据清除/更改另一个单元格并将其应用于所有行来清除单元格 [英] Clearing cells based on clearing/changing another cell and applying it to all rows

问题描述

我在同一张Excel工作表上有两个表.
第一个表名为Cabinet，其范围为$ A $ 12:$ AN $ 29
第二个表名为LaminatedBench，范围为$ A $ 33:$ AN $ 50

I have two tables on the same excel sheet.
The first table is named Cabinet, and its range is $A$12:$AN$29
The second table is named LaminatedBench, and its range is $A$33:$AN$50

对于内阁"表:

我正在尝试:
当清除/更改A12时，清除单元格:B12，C12，D12和H12.
当清除/更改B12时，清除单元格:C12，D12和H12.
当清除/更改C12时，清除单元格:D12&H12.

I am trying to:
When A12 is cleared/changed, clear cells: B12, C12, D12 & H12.
When B12 is cleared/changed, clear cells: C12, D12 & H12.
When C12 is cleared/changed, clear cells: D12 & H12.

类似地:
当清除/更改A13时，清除单元格:B13，C13，D13和H13.
当清除/更改B13时，清除单元格:C13，D13和A.H13.
当清除/更改C13时，清除单元格:D13&D.H13.
&这适用于从第12行到第29行的所有行.

Similarly:
When A13 is cleared/changed, clear cells: B13, C13, D13 & H13.
When B13 is cleared/changed, clear cells: C13, D13 & H13.
When C13 is cleared/changed, clear cells: D13 & H13.
& This applies to all the rows from Row12 to Row29.

对于表"LaminatedBench":

For the table "LaminatedBench":

我正在尝试:当清除/更改A33时，清除单元格:B33，C33，D33，E33，F33，G33，H33和L33.
当清除/更改B33时，清除单元格:C33，D33，E33，F33，G33，H33和L33.
当清除/更改C33时，清除单元格:D33，E33，F33，G33，H33和L33.
当清除/更改D33时，清除单元格:E33，F33，G33，H33和L33.
当清除/更改E33时，清除单元格:F33，G33，H33和L33.
清除/更改F33时，清除单元格:G33，H33和L33.
清除/更改F33时，清除单元格:H33&.L33.
&这适用于从Row33到Row50的所有行.

I am trying to: When A33 is cleared/changed, clear cells: B33, C33, D33, E33, F33, G33, H33 & L33.
When B33 is cleared/changed, clear cells: C33, D33, E33, F33, G33, H33 & L33.
When C33 is cleared/changed, clear cells: D33, E33, F33, G33, H33 & L33.
When D33 is cleared/changed, clear cells: E33, F33, G33, H33 & L33.
When E33 is cleared/changed, clear cells: F33, G33, H33 & L33.
When F33 is cleared/changed, clear cell: G33, H33 & L33.
When F33 is cleared/changed, clear cell: H33 & L33.
& This applies to all the rows from Row33 to Row50.

我在第一行的表1中使用以下代码.我将添加更多表，并且必须为每行写几行代码.

I am using the below code for table 1 for the first row. I will be adding many more tables and would have to write several lines of codes for each row.

如何在不添加每一行代码的情况下进行调整?

How do I adjust it without adding a line of code of each row?

Private Sub Worksheet_Change(ByVal Target As Range)
End If
If Not Intersect(Target, Range("$A12")) Is Nothing Then
    Range("B12:D29").ClearContents
    Range("H12:H29").ClearContents
End If
If Not Intersect(Target, Range("$B12")) Is Nothing Then
    Range("C12:C29").ClearContents
    Range("H12:H29").ClearContents
End If
If Not Intersect(Target, Range("$A33")) Is Nothing Then
    Range("B33:H50").ClearContents
    Range("L33:L50").ClearContents
End If
If Not Intersect(Target, Range("$B33")) Is Nothing Then
    Range("C33:H50").ClearContents
    Range("L33:L50").ClearContents
End If
If Not Intersect(Target, Range("$C33")) Is Nothing Then
    Range("D33:H50").ClearContents
    Range("L33:L50").ClearContents
End If
If Not Intersect(Target, Range("$D33")) Is Nothing Then
    Range("E33:H33").ClearContents
    Range("L33:L50").ClearContents
End If
If Not Intersect(Target, Range("$E33")) Is Nothing Then
    Range("F33:H50").ClearContents
    Range("L33:L50").ClearContents
End If
If Not Intersect(Target, Range("$F33")) Is Nothing Then
    Range("G33:H50").ClearContents
    Range("L33:L50").ClearContents
If Not Intersect(Target, Range("$G33")) Is Nothing Then
    Range("H33:H50").ClearContents
    Range("L33:L50").ClearContents
End If
End If
End Sub

推荐答案

在桌子柜子里

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDB As Range
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("a12", "c29")) Is Nothing Then
        Set rngDB = Range(Target.Offset(, 1), Range("d" & Target.Row))
        Set rngDB = Union(rngDB, Range("h" & Target.Row))
        rngDB.ClearContents
    End If
End Sub

在LaminatedBench中

In LaminatedBench

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDB As Range
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("a33", "d50")) Is Nothing Then
        Set rngDB = Range(Target.Offset(, 1), Range("h" & Target.Row))
        Set rngDB = Union(rngDB, Range("L" & Target.Row))
        rngDB.ClearContents
    End If
End Sub

这篇关于根据清除/更改另一个单元格并将其应用于所有行来清除单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！