使用VBA隐藏单元格 [英] Hiding cells by using VBA

问题描述

我正在使用以下代码在Excel VBA中隐藏所需的单元格.

I am using the following code to hide the required cells in Excel VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell   As Range
    Set Cell = Range("$F$26")
    If Not Application.Intersect(Cell, Range(Target.Address)) Is Nothing Then
        If Range("F26").Value < 2 Then
            Rows("39:61").EntireRow.Hidden = True
            ElseIf Range("F26").Value < 3 Then
            Rows("47:61").EntireRow.Hidden = True
            ElseIf Range("F26").Value < 4 Then
            Rows("55:61").EntireRow.Hidden = True
            Else: Rows("39:61").EntireRow.Hidden = False
        End If
    End If
End Sub

当我按降序排列值(例如4、3、2、1)时，它可以完美工作.但是在输入1之后，如果我打算切换到2或3(而不是4).然后，它不会显示第二个和第三个面板组中的单元格.但是，如果我输入4，则它将再次显示所有面板组.我附上了下面的屏幕截图.

It works perfectly when I put values in the descending order (like 4, 3, 2, 1). But after putting 1, if I am plan to switch to 2 or 3 (but not 4). Then it doesn't show the cells in second and third panel group. But if I put 4, then it again shows all panel groups. I attached the screenshots below.

推荐答案

您必须从头开始重新评估Range("$ F $ 26")中具有新值的后续调用；例如使所有内容可见，然后决定隐藏什么.

You've got to re-evaluate subsequent calls with new values in Range("$F$26") from scratch; e.g. make everything visible and then decide what gets hidden.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("F26"), Range(Target.Address)) Is Nothing Then
        Application.ScreenUpdating = False
        Rows("39:61").EntireRow.Hidden = False
        Select Case Range("F26").Value
            Case Is < 2
                Rows("39:61").EntireRow.Hidden = True
            Case 2
                Rows("47:61").EntireRow.Hidden = True
            Case 3
                Rows("55:61").EntireRow.Hidden = True
        End Select
        Application.ScreenUpdating = True
    End If
End Sub

我已将您的病情评估更改为选择案例声明，并删除了不必要的Cell变量.

I've changed your condition evaluation to a Select Case statement and removed the Cell variable as unnnecessary.

当Range("F26")为空白时，我找不到任何意外情况.目前，它属于< 2条件.

I could not find any contingency for when Range("F26") was blank. Currently, that falls under the < 2 condition.

这篇关于使用VBA隐藏单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！