Tech Support Guy banner
Status
Not open for further replies.

Show/Hide columns based on cell contents

997 views 4 replies 2 participants last post by  Lambernut 
#1 · (Edited)
Hi Guys,

Apologies for repeating an old question, but I am trying to build my spreadsheet so that it auto-hides any rows were there is zero premium outstanding for a client.

I had gone through previous answers, and I had tried to lift and amend some VBA code to achieve this. However, somewhere within this code or the way I've implemented it, there is an error, because it's not showing of hiding any rows.

Can you please take a look at the attached and fix it (in order to hide all rows with a zero in column F), and maybe point out what I was doing wrong or omitting. I have anonymised my data.

Many Thanks
John
 

Attachments

#2 ·
Just reoplace this code in the worksheet:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("F13:F94")) Is Nothing Then
        Application.EnableEvents = False
        Debug.Print Range("F13:F94").SpecialCells(xlCellTypeVisible).Count
        Select Case Range("F13:F94").SpecialCells(xlCellTypeVisible).Count <> 82
        Case Is = True
            Range("F13:F94").EntireRow.Hidden = False
        Case Else
            Dim rng As Range
            Application.ScreenUpdating = False
            For Each rng In Range("F13:F94")
                If rng.Value = 0 Then
                    Cells(rng.Row, 1).EntireRow.Hidden = True
                End If
            Next rng
            Application.ScreenUpdating = True
        End Select
        Application.EnableEvents = True
    End If
End Sub
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top