在Worksheet_Change上隐藏/取消隐藏带有空行的单元格 [英] Hide/Unhide cells with empty rows on Worksheet_Change

问题描述

我有两列数据通过每个单元格中的公式从工作簿中的其他工作表的其他工作表中的数据拉入工作表。

I have two columns of data that is pulled into a worksheet from data on other sheets elsewhere in the workbook via a formula in each cell...

第一个列A列具有是，否或从另一张表中通过公式拉入的数据。

The first column, Column A, has either a Yes, No or is blank from data that is pulled in via a formula from another sheet.

第二列列B也有数据从其他地方拉入，但每行都有数据。

The second column, Column B, also has data pulled in from elsewhere but every row has data in it.

我希望做的是隐藏列A中没有任何行的任何行。列A中的数据应该是可见的。我想通过工作表_change事件更新，使用VBA输入数据时出现在列A。

What I hope to do is hide any rows that does not have anything in column A. Any rows with data in column A should be visible. I'd like this to be updated via the worksheet_change event using VBA when data is entered that appears in column A.

非常感谢你可以帮助。

Many thanks if you can help.

推荐答案

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
        Application.EnableEvents = False
dim lrow as Integer
dim i as Integer
     lrow = Cells(1, 2).End(xlDown).Row
    For i = 1 To lrow
    If Cells(i, 1) = 0 Then
    Rows(i).Select
    Selection.EntireRow.Hidden = True
    End If
    Next
        Application.EnableEvents = True
    End Sub

您必须将其插入到工作表的代码上。右键单击工作表名称，然后按查看代码并将其另存为宏启用。
当对列a进行更改时，它被激活。

You have to insert this on the code of the sheet. right click the sheet name and press the view code and save it as macro enable. It gets activated when changes have done to column a.

这篇关于在Worksheet_Change上隐藏/取消隐藏带有空行的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！