当第一列改变时，自动计算(或删除)Excel中的行

I have a big table, where first columns X is "input column" and range it's changing.

我有一个大表格，第一列X是“输入列”，范围在变化。

Y - There are more formulas and functions (Vlookup) and 1st column X is a lookup value, and then other columns are calculated from other sheets.

Y -有更多的公式和函数(Vlookup)，第一列X为查找值，然后从其他表中计算其他列。

  | A | B | C | D | E
1 | X | Y | Y | Y | Y
2 | X | Y | Y | Y | Y
3 | X | Y | Y | Y | Y
4 | X | Y | Y | Y | Y

I am inserting (and deleting) more X values (actual data) and then I use "double click" for all other Y columns to be calculated, BUT it's not good because the X range is not the same. I tried to convert it to table "Ctrl-T", but it's not working very good for me. Maybe I don't use it properly.

我插入(并删除)更多的X值(实际数据)，然后对所有其他要计算的Y列使用“双击”，但这并不好，因为X的范围不同。我试着将它转换为表“Ctrl-T”，但它对我的效果不是很好。也许我用得不好。

Problem:

问题:

If I paste a new X column, I need other Y columns to be automatically calculated OR if I delete few X rows, other Y should be also deleted. Now I get something like this:

如果我粘贴一个新的X列，我需要自动计算其他的Y列，或者如果我删除几个X行，其他的Y也应该被删除。现在我得到了这样的结果:

  | A | B   | C   | D   | E
1 | X | Y   | Y   | Y   | Y
2 | X | Y   | Y   | Y   | Y
3 |   | N/A | N/A | N/A | N/A
4 |   | N/A | N/A | N/A | N/A

or:

或者:

  | A | B   | C   | D   | E
1 | X | Y   | Y   | Y   | Y
2 | X | Y   | Y   | Y   | Y
3 | X |     |     |     |

What I need:

我所需要的东西:

If I remove X value I need automatically disappear Y values:

如果我删除X值，我需要自动消失Y值:

  | A | B | C | D | E
1 | X | Y | Y | Y | Y
2 | X | Y | Y | Y | Y

If I add X value I need automatically calculate Y values:

如果我增加X值，我需要自动计算Y值:

  | A | B | C | D | E
1 | X | Y | Y | Y | Y
2 | X | Y | Y | Y | Y
3 | X | Y | Y | Y | Y

Hope it's clear, thank you!

希望你能理解，谢谢!

2 个解决方案

#1

For Y Columns, you can add "IF" FORMULA

对于Y列，可以添加IF公式

=if(A1>0,*Y COLUMN FORMULA*,"")

#2

try changing formula to

试着改变公式

=iferror(*Y formula,"")

or if it's still slow and if you are changing only X Columns

或者如果它仍然很慢如果你只改变X列

you can use below code

您可以使用以下代码。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Count = 1 Then 'CHECK IF THERE IS ANY CHANGE ON X COLUMN
        If Target.Value = Empty Then 'CHECK IF X COLUMN HAS BEEN DELETED
            Rows(Target.Row).Delete 'IF X COLUMN IS DELETED, DELETS WHOLE ROW
        Else
            Cells(Target.Row - 1, 2).Resize(1, 4).Copy Cells(Target.Row, 2).Resize(1, 4) 'IF X COLUMN IS ENTERED OR MODIFIED COPIES ABOVE Y COLUMN FORMULAS
        End If
    End If
End Sub

#1