Hide Rows with empty range

Posted on 2012-08-21
Last Modified: 2012-08-22
Hello Experts,

I am hoping you will be able to assist me since I can not seem to find the answer on this one.

I am trying to write some code that will hide rows if the following criteria is met:
(this will be reside in the sheet activate routine)

In Range("A6:A100") - if there are 3 or more consecutive cells in the range that are empty (no numeric/character) then it hides entire row until the end of the range, row 100. And, as long as those cells are empty too, which will almost always be the case - but I would like the code to check it before it hides, just in case.

The ideal situation is for it to start hiding rows starting with the 3rd row, keeping the first 2 visible and hiding all rows until the end of range is met.  This will give me the opportunity to add a few items in each range without manually going through the process of unhiding rows.

I will duplicate this again for another range("A101:A200"). Then onto the others.

I have 7 ranges using this criteria.  I can duplicate for the others, once I am educated.

This is a template I created for end-users to key data (in other columns) - I recently expanded the range areas for future expansion - but I want to hide these rows when I do not need them.  I add or delete items on a routine basis - so I want it to be automated so I do not have to edit code on an on-going basis.

Thank you and I hope I explained this well enough for interpretation.
Question by:mike637
    LVL 10

    Accepted Solution

    I think the code below will do what you're asking for:

    Sub test()
        hideWhenEmpty ActiveSheet.Range("A6", "A100")
        hideWhenEmpty ActiveSheet.Range("A100", "A200")
    End Sub
    Private Sub hideWhenEmpty(rng As Range)
    Dim c As Range
    Dim hiding As Boolean
    Dim n As Integer
    Dim x As Integer
        hiding = False
        n = 0
        For Each c In rng.Cells
            If hiding Then
                c.Rows(1).EntireRow.Hidden = True
            ElseIf c.Value = "" Then
                n = n + 1
                If n = 3 Then
                    c.Rows(-1).EntireRow.Hidden = True
                    c.Rows(0).EntireRow.Hidden = True
                    c.Rows(1).EntireRow.Hidden = True
                    hiding = True
                End If
                n = 0
            End If
        Next c
    End Sub

    Open in new window


    Author Closing Comment

    Wow!!  Thank you very much.

    Incidentally - I just had to change the 2 lines to read false
    c.Rows(-1).EntireRow.Hidden = False
    c.Rows(0).EntireRow.Hidden = False

    And then it did exactly what it needed to do.

    Thank you very much!!
    LVL 10

    Expert Comment

    If you don't want the first two empty rows to be hidden then you can remove those two lines of code entirely.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now