CODEWITHSUNDEEP
vbaexcel
Matt Marshall
Matt Marshall

Reputation: 13

autohide rows in excel based on condition

I have found several responses to similar questions, but I can't make this work for the life of me. I am trying to automatically hide rows based on an input in a cell. I have data in columns A thru J, and I want to autohide any rows in which I input a value in column K. I am certain this can be done, but I am at my wit's end trying to get the VBA macros to work!

Any help would be greatly appreciated.

Thanks!

Upvotes: 1

Views: 2838

Answers (3)

ChrisB
ChrisB

Reputation: 3205

Here's a variation on the answer from @dinotom. It hides the entire row when you enter a value in column K, but not if you delete a value from column K.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 11 And Target.Value <> "" Then ' Column K is column number 11
        Target.EntireRow.Hidden = True
    End If
End Sub

Upvotes: 0

ChrisB
ChrisB

Reputation: 3205

Try this. It loops through every cell in column K and hides cells with any text.

Sub AutoHideRows()
'Get the last row of the used range
    Dim LastRow As Long
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

'Loop through the range looking for non-blank values, and hide those rows
    Dim c As Range
    For Each c In Range("K1:K" & LastRow)
        If c.Value <> "" Then
            c.EntireRow.Hidden = True
            Else
            c.EntireRow.Hidden = False
        End If
    Next c
End Sub

If your first row of data contains a column header and you want to exclude it from the filter, change this line of code:

For Each c In Range("K1:K" & LastRow)

to this:

For Each c In Range("K2:K" & LastRow)

Hope this helps!

Upvotes: 0

dinotom
dinotom

Reputation: 5162

All you need is a worksheet change event in your sheet. Put this code in the code section of your sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 11 Then ' Column K is column number 11
        Target.EntireRow.Hidden = True
    End If
End Sub

When you change the value in any cell in column k it will hide the entire row.
If you only want it to work in the UsedRange you can edit the code to work that way as well.

Upvotes: 2

Related Questions