Excel VBA - 隐藏所有行，其中value =活动单元格偏移单元格值？ [英] Excel VBA - Hide All Rows where value = Active Cell Offset Cell Value?

问题描述

我有一张如此布置的电子表格：

I have a spreadsheet laid out like so:

A        B            C
12       Row1         Click to Hide
12       Row2         Click to Hide
5        Row3         Click to Hide
4        Row4         Click to Hide
12       Row5         Click to Hide

我使用以下vba代码来尝试隐藏所有与用户点击隐藏的activeCell行共享相同数字的行（在A列中）。

I am using the following vba code to try and hide all rows which share the same number (in Column A) as the activeCell Row which the user has clicked to hide.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(ActiveCell, Range("C:C")) Is Nothing And ActiveCell.Value = "Click to Hide" Then


Dim Cell As Range

Application.ScreenUpdating = False

Range("A4").End(xlDown).Select


For Each Cell In Range(ActiveCell, "A4")
Cell.EntireRow.Hidden = ActiveCell.Offset(0, -2)

Next
Application.ScreenUpdating = True



End If

End Sub

所以例如，如果用户点击单击以隐藏'在行1中的列C中，列A中所有行号为12的所有行都应该被隐藏。

So for instance, if a user clicks 'Click to Hide' in column C on Row1, all rows with the number 12 in column A should be hidden.

目前我收到错误代码，1004 offset的对象或范围失败。
请问有人会告诉我我哪里错了吗？感谢提前。

At the moment i am getting an error code, 1004 offset of object or range failed. Please can someone show me where i am going wrong? Thanks in advance.

推荐答案

这将隐藏替代行，但隐藏使用ActiveCell隐藏行：

This will hide the alternative rows, but hide hide the row with ActiveCell:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C:C")) Is Nothing And Target.Value = "Click to Hide" Then
        valu = Cells(Target.Row, 1).Value
        For i = 1 To ActiveSheet.UsedRange.Rows.Count
            If Cells(i, 1).Value = valu Then
                Cells(i, 1).EntireRow.Hidden = True
            Else
                Cells(i, 1).EntireRow.Hidden = False
            End If
        Next i
        Target.EntireRow.Hidden = False
    Else
        Rows.Hidden = False
    End If
End Sub

这篇关于Excel VBA - 隐藏所有行，其中value =活动单元格偏移单元格值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！