根据选择隐藏行 [英] Hide rows based on choice

问题描述

我可以使用下拉菜单在单元格(C41:C59)中做出很多选择，下拉列表的值也可以在单元格(E41:E59)中看到.对于每个选择，我希望显示或隐藏不同的行.如果下拉列表为N/A，则隐藏，否则显示行.我无法解决的问题是，每个选择都有不同的行，并且行数也不同.因此，我尝试为每个选择创建一个代码，并且仅当E列中的单元格更改时才更改此代码.这是我到目前为止的内容，但是什么也没做.

I have a bunch of choices that can be made in the cells (C41:C59) using a dropdown, the value of the dropdown also is seen in the cells (E41:E59). For each choice I want different rows to show or hide. Hide if the dropdown is N/A else show the rows. The problem i can't get around is that every choice has different rows and als a different amount of rows. So I tried to make a code per choice and only change this when the cell in column E changes. This is what I have so far, but doesn't do anything.

If Not Application.Intersect(Target, Range("E41")) Is Nothing Then
        If Range("E41") = "N/A" Then
            [67:73].EntireRow.Hidden = True
        Else
            [67:73].EntireRow.Hidden = False
        End If
    End If

推荐答案

下面的代码是一个事件过程.当在其代码表中找到该过程的工作表上更改单元格时，它将运行.(该代码在该特定模块中的位置至关重要.)如果更改了一个单元格-忽略了多个同时发生的更改，例如可能由复制/粘贴操作引起的更改-该代码将检查修改后的单元格是否在C41范围内:C59或E41:E59.如果是这样，它将在修改后隐藏或显示同一工作表中的行，具体取决于修改后该单元格的值为"N/A".

The code below is an event procedure. It runs when a cell is changed on the worksheet in whose code sheet the procedure is found. (The location of the code in that particular module is paramount.) If a single cell was changed - ignoring multiple simultaneous changes such as might be caused by copy/paste action - the code will check if the modified cell was in the ranges C41:C59 or E41:E59. If so, it will hide or show rows in the same worksheet depending upon whether or the cell's value is "N/A" after modification.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 010

    Dim TriggerRange As Range
    Dim Rng As Range

    ' ignore simultaneous changes of many cells
    If Target.Cells.CountLarge > 1 Then Exit Sub

    Set TriggerRange = Application.Union(Range("C41:C59"), Range("E41:E59"))
    If Not Application.Intersect(TriggerRange, Target) Is Nothing Then
        Select Case Target.Row
            Case 41, 46, 59
                Set Rng = Range("67:73")
            Case 50 To 59
                Set Rng = Range(Rows(67), Rows(73))
            Case Else
                Set Rng = Range(Rows(67), Rows(73))
        End Select
        Rng.Rows.Hidden = (Target.Value = "N/A")
    End If
End Sub

在此代码中，总是隐藏或显示相同的行.该代码用于演示如何根据更改的单元格所在的行来指定不同的行范围，并根据您的喜好使用不同的语法.

In this code always the same rows are hidden or shown. The code serves to demonstrate how you could specify different row ranges depending upon which row the changed cell is in, using different syntax depending upon your preference.

这篇关于根据选择隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！