Hide Rows if Cells in a Column are blank

[cmerc2019]

Hello everyone,

I specifically made an account so I can figure out how to do this in Excel. So what Im trying to do is hide the rows when cells from a range of columns does not have data in it.

Example: K4:Q4 will be filled in with data and if theres no data on this entire column then I would like to hide rows A2:A8 because that encompasses information for each of those data.

 

[cmerc2019]

I don't know if am doing something wrong but it is not working. I found a code online but its only hiding partial of what I need hidden.

Here it is:
Sub HideRows()
ApplicationScreenUpdatng =False
Dim i As Integer
Dim RStart As Range
Dim REnd As Range
Set RStart = Range("A2")
Set REnd = Sheets("YourSheetName").Range("A655356").End(xlUp).Offset0,3)
Range(RStart, REnd).Select
On Error Resume Next
With Selection
.EntireRow.Hidden = False
For i= To .Rows.Coun
If WorksheetFunction.CountBlank(.Rows(i)) = 4 Then
.Rows(i).EntireRow.Hidden = True
End if
Next i
End With
Set RStart = Nothing
Set REnd = Nothing
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

You never mentioned if the script I provide worked.

You said:

I want to hide rows 2 to 8 if Range("K4:Q4") are empty

My script does exactly that. If all cells in Range("K4:Q4") are empty. Rows 2 to 8 are hidden.

K4:Q4 are seven ranges.
If all 7 ranges are empty then Rows 2 to 8 are hidden.
​

 

[cmerc2019]

Sorry, It did work! I appreciate the help so much. How do I extend the code if I have more rows to consider? Previously it was K4:Q4, what if I have 50 or 100 more rows grouped into 8 rows at a time. Example of it would be to check K11:Q11 if its blank then hide Rows 9 to 15, K18:Q18 if blank then hide Rows 16:22, then K25:Q25 if blank then hide Rows 23 to 29 so on and so forth.

 

[My Aswer Is This]

How do we know when to stop?
You said on and on

 

[cmerc2019]

600 Rows is the further my data goes to. If you could just tell me what I need to change in the code then I could try myself.

 

[My Aswer Is This]

Try running this script.
I have it set to run the script 5 times you can modify 5 to how many times you need to run it.

I have it set to color the rows Red instead of hiding them for your first test.

If this works properly coloring the rows Red.
Then remove that line of code and use the line of code where it says Hidden

Sub Hide_Rows()
'Modified  7/24/2019  1:17:51 PM  EDT
Application.ScreenUpdating = False
Dim x As Long
Dim r As Range
x = 0
Dim i As Long
Range("K4:Q4").Select
    For i = 1 To 5 ' change the 5 to the number of times we need to do this
        For Each r In Selection
            If r.Value = "" Then x = x + 1
    Next
    If x = 7 Then
        Rows(Selection.Row).Offset(-2).Resize(7).Interior.Color = vbRed
            ' The '   mark tells the script to ignor this line of code. So remove the ' mark to run the hide line of code
                'Rows(Selection.Row).Offset(-2).Resize(7).Hidden = True
    End If
Selection.Offset(7).Select
x = 0
Next

Application.ScreenUpdating = True
End Sub

 

[cmerc2019]

Try running this script.
I have it set to run the script 5 times you can modify 5 to how many times you need to run it.

I have it set to color the rows Red instead of hiding them for your first test.

If this works properly coloring the rows Red.
Then remove that line of code and use the line of code where it says Hidden

Sub Hide_Rows()
'Modified  7/24/2019  1:17:51 PM  EDT
Application.ScreenUpdating = False
Dim x As Long
Dim r As Range
x = 0
Dim i As Long
Range("K4:Q4").Select
    For i = 1 To 5 ' change the 5 to the number of times we need to do this
        For Each r In Selection
            If r.Value = "" Then x = x + 1
    Next
    If x = 7 Then
        Rows(Selection.Row).Offset(-2).Resize(7).Interior.Color = vbRed
            ' The '   mark tells the script to ignor this line of code. So remove the ' mark to run the hide line of code
                'Rows(Selection.Row).Offset(-2).Resize(7).Hidden = True
    End If
Selection.Offset(7).Select
x = 0
Next

Application.ScreenUpdating = True
End Sub

Works Perfectly! One last thing, what if I want to hide 9 or 8 rows instead of 7 based on the same criteria, where do I change it to make the code work?

 

[My Aswer Is This]

Untested. But try changing every 7 you see and change it to a 9

 

[My Aswer Is This]

Do not change this 7

If x = 7 Then
​

 

[cmerc2019]

Thanks for your patience and all the help!