CODEWITHSUNDEEP
excelvba
Duckborg
Duckborg

Reputation: 1

Search a column for specific value and then hide rows if value is found

I want to be able to search a specific column for a specific value and then hide a row if that value is found. So far I'm working with the following and wondered if there was to search a Target Range instead of Target Column:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = "2" Then 'I want to search Column B
        'I want to search for the value 2
        If Target.Value = "2" Then 
            ActiveSheet.Rows(10).EntireRow.Hidden = False 'I want to reveal the hidden row 10 if Value 2 is found
        ElseIf Target.Value <> 2 Then 'If there is no value of 2
            ActiveSheet.Rows(10).EntireRow.Hidden = True 'Keep row 10 hidden
        End If
    End If
End Sub

Upvotes: 0

Views: 180

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

In general, you can use the Intersection of your range of intereset and Target to see if any cells of interest changed.

Other improvements:

  • ActiveSheet referes to the active sheet, which may or may not be the sheet containing the code. In a worksheet code behind module Me refers to that sheet
  • Numbers and strings that look like numbers are not the same thing. In some cases Excel might do am implicit conversion behind the scenes, but not always

Here's your code, refactored

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RangeOfInterest As Range
    Dim TargetRangeOfInterest As Range
    Dim cl As Range
    Dim SearchValue As Variant
    
    'I want to search for the value 2
    '~~~ you probably want to search for the number 2, not the string "2"
    SearchValue = 2
    
    Set RangeOfInterest = Me.Range("B1:B10") '<~~ adjust to suit your needs
    
    Set TargetRangeOfInterest = Application.Intersect(RangeOfInterest, Target)
    If Not TargetRangeOfInterest Is Nothing Then
        With Me.Rows(10).EntireRow
            For Each cl In TargetRangeOfInterest.Cells
                If cl.Value2 = SearchValue Then
                    ' Reads are faster than Writes, so only change the Hidden state if required
                    If .Hidden Then
                        .Hidden = False 'I want to reveal the hidden row 10 if Value 2 is found
                    End If
                Else  'If there is no value of 2
                    If Not .Hidden Then
                        .EntireRow.Hidden = True 'hide row 10
                    End If
                End If
            Next
        End With
    End If
End Sub

Upvotes: 1

Related Questions