Hide all Rows except matching value

0
votes

I'm working with some data in excel spanning B9:AJ1108 - so multiple rows and columns. I am looking to hide all rows except where the value in column B matches the number in cell C5. I've tried multiple and can only just about get everything to hide but the unhiding is the issue. I understand how to hide all and how to unhide all. What I need help with is how to hide all and then unhide if something matches the value in C5.

Code so far:

Private Sub CommandButton2_Click()

Worksheets("Employee information").Range("B9:B1108").Rows.Hidden = False

End Sub

Private Sub CommandButton1_Click()

Worksheets("Employee information").Range("B9:B1108").Rows.Hidden = True
'Need to put in the argument to search for C5 value

End Sub

I would also like this to be button controlled but I don't know if that is a case of creating a module or just code within the sheet?

2
excelvba
Why don't you just use the filter? - Variatus
Because the requirement is the use of macros - DNM
Could you share the code you've got so far? The one hiding all rows? - VBasic2008
You can achieve this using filters in a macro. You can assign a macro to a button that filters and un-filters the data. But please add what you have tried so far. - Gangula
I have updated with the code so far (extremely basis as I haven't used VBA before and I would personally use the filter instead) - DNM

2 Answers

0
votes

For unhiding the rows you can use "Rows.EntireRow.Hidden = False" If you want to use a button for the macro to get executed, create a button and excel will ask you which macro you want to get when you click the button.

value= Worksheets("Employee information").cells(5,3).value

That will give you the value of the cell C5, now you need to go through the rows and look for this value.

0
votes

Hide Rows Not Containing Criteria in Column

Private Sub CommandButton1_Click()

    With Worksheets("Employee information")
        ' Define Criteria (restrict to numbers).
        Dim Criteria As Variant
        Criteria = .Range("C5").Value
        If Not IsNumeric(Criteria) Then
            Exit Sub
        End If
        ' Define Criteria Range.
        Dim rng As Range
        Set rng = .Range("B9:B1108")
    End With
    
    ' Declare additional variables.
    Dim hRng As Range     ' Hide Range
    Dim cel As Range      ' Current Cell (in Source Range)
    Dim CurVal As Variant ' Current Value (of Current Cell in Source Range)
    
    ' Create a union (Hide Range) of all the cell ranges
    ' that do not contain Criteria.
    For Each cel In rng.Cells
        ' Evaluate Current Value.
        CurVal = cel.Value
        If IsNumeric(CurVal) Then
            If CurVal = Criteria Then
                GoTo NextCell ' Match found: do nothing.
            End If
        End If
        ' Match not found: add Current Cell to Hide Range.
        If Not hRng Is Nothing Then
            Set hRng = Union(hRng, cel)
        Else
            Set hRng = cel
        End If
NextCell:
    Next cel
        
    ' Hide rows of Hide Range.
    If Not hRng Is Nothing Then
        hRng.Rows.Hidden = True
    End If

End Sub