1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Show/Hide columns based on cell contents

Discussion in 'Business Applications' started by Lambernut, Nov 14, 2017.

Thread Status:
Not open for further replies.
Advertisement
  1. Lambernut

    Lambernut Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    2
    First Name:
    John
    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
     

    Attached Files:

    Last edited: Nov 14, 2017
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    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
    
    
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    You may remove the line
    Code:
    Debug.Print Range("F13:F94").SpecialCells(xlCellTypeVisible).Count
    
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I see it wasn't that urgent, but anyway. you'll run into issues if you want to update a cell in column F.
    I changed the button to hide/unhide and removed the sheet's trigger, that's a p in the A
     

    Attached Files:

    Lambernut likes this.
  5. Lambernut

    Lambernut Thread Starter

    Joined:
    Nov 14, 2017
    Messages:
    2
    First Name:
    John
    I'm really sorry for the delay response, but thank you very much for taking the time to help me. This has worked just as planned so thank you!
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1199446

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice