Worksheet Change in Excel [Hiding Particular Rows]

2019-09-01 12:12发布

Here's the screenshot of the file. enter image description here

I am using the following code in VBA to hide few rows automatically with the change of values in a particular cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell   As Range
    Set cell = Range("G1")
    If Not Application.Intersect(cell, Range(Target.Address)) Is Nothing Then
        If Range("G1").Value > 50 Then
            Rows("12:17").EntireRow.Hidden = False
        Else
            Rows("12:17").EntireRow.Hidden = True
        End If
    End If
End Sub

I have changed options to macro enable. The code should work but it is not getting there.

1条回答
唯我独甜
2楼-- · 2019-09-01 12:42

In the VBE, tap [ctrl]+G to get to the Immediate window and paste this in Application.EnableEvents = True then hit enter at the end-of-line.

You code can be appreciably trimmed down to the following.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("G1")) Is Nothing Then
        Rows("12:17").EntireRow.Hidden = CBool(Range("G1").Value <= 50)
    End If
End Sub

Make sure that you are in a worksheet code sheet and not the ThisWorkbook code sheet or a module code sheet. Right-clicking the worksheet's name tab and choosing View Code is the quickest way to make sure you are in the right place.

查看更多
登录 后发表回答