Excel VBA - Hide All Rows where value = Active Cell Offset Cell Value?

Question

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

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

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.

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.

get rid of this - it's not necessary: Range("A4").End(xlDown).Select — MacroMarc
1. Don't use ActiveCell use Target as the cell in whcih they click is passed to the sub. 2. Cell.EntireRow.Hidden = Cell.Value = Target.Offset(0, -2).value 3. For Each Cell In Range("A4", Cells(Rows.Count,1).End(xlUp)) — Scott Craner
@ScottCraner - perfect this works great. i'm not sure i can upvote comments, if you would like to post this briefly as a answer i will be happy to upvote. thanks — M.Doe
@ScottCraner is there also a way i could hide all the rows matching the target row values in column A - but without hiding the actual target row itself? so say there were 4 rows with number 12 in column A. If i click row1 it would hide rows2,3 and 4 without hiding row 1? — M.Doe

Gary's Student Gary's Student · Accepted Answer · 2016-10-05T22:07:14

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 - Hide All Rows where value = Active Cell Offset Cell Value?

1 Answers