Hide Rows with empty range

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.
Who is Participating?
tdlewisConnect With a Mentor Commented:
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

mike637Author Commented:
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!!
If you don't want the first two empty rows to be hidden then you can remove those two lines of code entirely.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.