Auto Hiding rows based on range/data present or not present.

I need a macro to run with a button.

If I have a range of say A1:T8

Rows 1 and 2 are heading rows and rows 3-8 have data. If row 3 is empty of data then rows 1-8 are hidden. If row 3 has data then check each row after it and hide rows with no data.

Then I need a simple macro for another button to unhide all hidden rows.

Thank you in advance for the help.

I checked this code. Its a good start. I noticed the range when checking for empty rows isnt checking the entire row. A range from A to T is needed when checking for content.

Here is the code for for any willing to have a look at it: The highlighted section is where the range is needed.

Sub HIDEROWS()
     Dim i As Long
     
     For i = 4 To 8
         If Range("A3") = "" Then
             Range("A1:T8").Select
             Selection.EntireRow.Hidden = True
             Exit For
         Else
             If Range("A" & i) = "" Then
                 Rows(i).Select
                 Selection.EntireRow.Hidden = True
             End If
         End If
     Next
 End Sub
 
 Sub UNHIDEROWS()
     Dim i As Long
     
     For i = 8 To 1 Step -1
         Rows(i).Select
         Selection.EntireRow.Hidden = False
     Next
End Sub
End Sub

bump :P I'd really like to get this done today so I can finish my project. If any one can look I am very appreciative.

Tried this but it doesn't seem to work:

Sub HIDEROWS()
     Dim i As Long
     
     For i = 4 To 8
         If Row("3") = "" Then
             Range("A1:T8").Select
             Selection.EntireRow.Hidden = True
             Exit For
         Else
             If Range("A" & i) = "" Then
                 Rows(i).Select
                 Selection.EntireRow.Hidden = True
             End If
         End If
     Next
 End Sub
 
 Sub UNHIDEROWS()
     Dim i As Long
     
     For i = 8 To 1 Step -1
         Rows(i).Select
         Selection.EntireRow.Hidden = False
     Next
End Sub
End Sub

ok cool. the first chsnge I tried that too but I didn't know I'd need to make changes on the second red line. Thanks dredwolf.

Meh...that first red section dubugs as mismatch error.

ok will work on it asap

Thanks to both of you for the help.

Check this out. Its working now. See where I changed the : to ,

Sub HIDEROWS()
     Dim i As Long
     
     For i = 10 To 17
         If Range("A9,T9") = "" Then
             Range("A7:T17").Select
             Selection.EntireRow.Hidden = True
             Exit For
         Else
             If Range("A" & i & ",T" & i) = "" Then
                 Rows(i).Select
                 Selection.EntireRow.Hidden = True
             End If
         End If
     Next
 End Sub

uhh well I spoke too soon. I took it step further. But its still closing rows if column A is empty. So Im loading the latest version of my file with the macro in it so you can see it.

edit: its the calender page. I want to make the calender concise for viewing/printing purposes.





Multi_Family_Assistant.xlsm

Well the first header is the week day. So we always want it to display.

The next two rows are headers for the community/tech etc. So if there are entries in the cells below the community/tech header then we want the rows visible with data. If there are no entries then the community/tech headers are hidden along with the data cells for those headers.

If you change the last tuesday stop (doing so on the admin page) to monday you can see the row will not hide. but it is hiding that row when the data is on any day but monday. So thats the test. If column a below the headers is empty and yet there is data in the row then the row should not hide. Maybe you know another member on the forum who can help?

ohes nhoes lol ok ty any way.

lol "fat finger error." I know what ya mean. This looks hopeful. I will work with this code later today. Is it already set to hide the rows returning zero? If not then Im already going to be at a loss trying to implement this. I have no skills in any form of Excel coding.

Im lost. I don't know where to place the function or how to make it execute. Please advise.

That function will just return the number of the last column of a row that has a value or a 0 if there is no value in any column. You still need to write the loop to search each row to see if it is to be hidden or not. I'll write the HideRows function you've currently got attached to the Update Calendar Button if you want but I'll need some more information to make sure I understand what you want.

Originally Posted by raze

Rows 1 and 2 are heading rows and rows 3-8 have data. If row 3 is empty of data then rows 1-8 are hidden. If row 3 has data then check each row after it and hide rows with no data.

Then I need a simple macro for another button to unhide all hidden rows.

In the sample you uploaded, row 1 has a value of 11 and rows 2 thru 5 are blank, row 6 contains the weekdays, rows 7 and 8 contain the header information (Albert, Barton, etc) about what you'll be displaying in rows 9 thru 17 (and so on down the sheet). Going by what you put in the first post if row 3 is blank hide rows 3 thru 8? Or did you mean check the first row of each header section and hide rows without values for each header section? Would you want that header section hidden also? Rows 1 thru 5 are throwing me off, I'm not sure what data is supposed to be shown there or if they are just for spacing to keep the button from being inside the calendar.

Also, would you want the one button to make all rows showing data visible at the same time or a button to just show all rows regardless of data visible?

This is doing exactly what I need. Thank you very much

You're welcome.