CODEWITHSUNDEEP
vbaexcelexcel-formula
user118282
user118282

Reputation: 25

Hide Rows when a cell is populated

So I have a list of data(tasks) in cells A2 to E2, and in column F is the option for my team to assign their name to it. What I'm trying to make happen, is as a name is inputted in F2, F3, F4.... the respective row disappears.

Example.

F1="Bob" , then row 1 dissapears.

This is what I have so far, but I have a feeling I might be going in the wrong direction with it. 

Option Explicit
Private Sub Worksheet_Activate()
    Dim r As Range, c As Range
    Set r = Range("a1:a299")
    Application.ScreenUpdating = False
    For Each c In r
        If Len(c.text) = 0 Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

I'm also not sure if this will update it straight away, or I'll have to run the macro everytime. If I've got it right, it should do the first.

Upvotes: 0

Views: 242

Answers (1)

Sorceri
Sorceri

Reputation: 8033

as per my comment

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'check to make sure we only have 1 cell
    If Target.Count = 1 Then
        'check the column
        If Target.Column = 6 Then 'F column
            'check text length and if greater then 0 hide the row
            If Len(Target.Text) > 0 Then
                Target.EntireRow.Hidden = True
            Else
                Target.EntireRow.Hidden = False
            End If
        End If
    End If
End Sub

Upvotes: 1

Related Questions