CODEWITHSUNDEEP
excelvba
user3722957
user3722957

Reputation: 87

Hide rows in spreadsheet range containing a value

I have a spreadsheet containing a list of all possible project tasks for different types of project in a range, and a column in the range which states to which project it relates.

In cell A1 I have a dropdown box of different project types - containing the values "Custom API" and "Custom File".

The data range is C3:E10, and example data is shown in the Example Data.

Column A: Task name
Column B: Task Duration
Column C: Task Owner
Column D: Project Type

What I'd like from some vba code is:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$A$1" and Target.Cells.Count = 1 Then

    Application.ScreenUpdating = False
    Range("B4:E10").EntireRow.Hidden = False
    Dim taskList as Range
    Set taskList = Range(Range("E4"),Range("E4").End(xlDown))

    Dim taskCheck as Range
    For each taskCheck in taskList
        taskCheck.EntireRow.Hidden = taskCheck <> Target
    Next

  End If

End Sub

Example data

Upvotes: 1

Views: 84

Answers (2)

user10862412
user10862412

Reputation:

You are really just setting up an AutoFilter without header dropdowns.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then

        Range("B4:E10").EntireRow.Hidden = False
        If AutoFilterMode Then AutoFilterMode = False
        With Range(Cells(3, "E"), Cells(4, "E").End(xlDown))
            .AutoFilter field:=1, Criteria1:=Array(Cells(1, "A").Value, "All"), _
                        Operator:=xlFilterValues, VisibleDropDown:=False
        End With

    End If

End Sub

You can clear the AutoFilter and show all values by adding an asterisk (e.g. *) to your list of values for the A1 dropdown.

Upvotes: 1

Variatus
Variatus

Reputation: 14383

Please try this code. Make sure that the spelling of the items in A1 match with that in the test column.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 03 Jan 2019

    ' set these two constants to match your sheet
    Const FirstDataRow As Long = 4
    Const TestClm As String = "E"

    Dim Rng As Range
    Dim Arr As Variant
    Dim Tgt As String
    Dim C As Long
    Dim R As Long

    ' (If the address is $A$1 it can't have more than one cell)
    If Target.Address = "$A$1" Then
        Tgt = Target.Value
        Rows.Hidden = False
        C = Columns(TestClm).Column
        Set Rng = Range(Cells(FirstDataRow, C), Cells(Rows.Count, C).End(xlUp))
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        With Rng
            Arr = .Value
            For R = 1 To UBound(Arr)
                Rows(R + FirstDataRow - 1).Hidden = Not (CBool(StrComp(Arr(R, 1), Tgt, vbTextCompare) = 0) Or _
                                                    CBool(StrComp(Arr(R, 1), "All", vbTextCompare) = 0))
            Next R
        End With
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If
End Sub

Upvotes: 0

Related Questions