CODEWITHSUNDEEP
vbaexcel
Sara Martínez Comesaña
Sara Martínez Comesaña

Reputation: 9

Hide a row (in a list) based on drop-down multiple selection on each of the rows (not on a single cell)

I have a list of "activities" in column B and each of them has a drop-down list for the status in column C. For each activity I can select "Done, In progress, TBD, Cancelled". What I want is to hide a row automatically (not filtering) every time I choose the status "Cancelled" in the drop-down (located in the same row).

Screenshot

The code used is below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rng As Range
Set rng = Target.Parent.Range("C2:C21")
If Target.Address <> Range("C2:C21").Address Then
    Exit Sub
Cells.EntireRow.Hidden = False
Select Case Range("C2:C21")
    Case "Cancelled":
        Range("2:21").EntireRow.Hidden = True - ***I want to hide only those rows in which "Cancelled" is selected.***
    Case "Done":
        Range("2:21").EntireRow.Hidden = False - ***I want the rows to unhide if either "Done","In progress" or "TBD" is selected.***
End Select

End Sub

It's probably a terrible code for what I want to do...

Any idea on how to improve this?

Thanks a lot in advance! :)

Sara

Upvotes: 0

Views: 1047

Answers (2)

Vityata
Vityata

Reputation: 43593

This is another option, skipping the If and Select Case:

Private Sub Worksheet_Change(ByVal target As Range)

    Dim myRng As Range
    Dim myCell As Range
    Set myRng = Range("C2:C21")

    If Not Intersect(myRng, target) Is Nothing Then
        Cells.EntireRow.Hidden = False            
        For Each myCell In myRng
            myCell.EntireRow.Hidden = CBool(myCell = "Cancelled")
        Next myCell            
    End If

End Sub

The "beauty" is that the If condition is eliminated and the .Hidden is assigned to a direct evaluation of myCell = "Cancelled";

Furthermore, the code is in a worksheet, as far as the _SelectionChange event is used. Then the parent worksheet of the range could be omitted, as far as it is taking the worksheet in which the code resides. So - Target.Parent.Range could be nicely skipped.

Upvotes: 0

newacc2240
newacc2240

Reputation: 1425

  1. The rng object has never been used.
  2. If you want to check the selection is in Range("C2:C21") or not, use a Intersect function.
  3. The If statement can be completed without an End If only if it is one-lined. Otherwise you have to put an End If at the end.
  4. Range("2:21").EntireRow.Hidden means every rows in 2:21 are going to be hidden.

Code:

Private Sub Worksheet_SelectionChange(ByVal target As Range)

    Dim rng As Range, cel As Range
    Set rng = target.Parent.Range("C2:C21")

    If Not Intersect(rng, target) Is Nothing Then
        For Each cel In rng
            cel.EntireRow.Hidden = IIf(cel.Value = "Cancelled", True, False)
        Next cel
    End If
End Sub

I'm not sure the version below is fitted or not. In my opinion, generally this macro should only be run after something is changed, and we can only check the row who has been changed, other rows should remain the same state.

Private Sub Worksheet_Change(ByVal target As Range)    
    Dim rng As Range, cel As Range
    Set rng = target.Parent.Range("C2:C21")
    If Not Intersect(rng, target) Is Nothing Then
        target.EntireRow.Hidden = IIf(target.Value = "Cancelled", True, False)
    End If
End Sub

Upvotes: 1

Related Questions