Hiding Rows based on Cell Value

[Davis10]

I have a worksheet I use for conferences to estimate travel costs for attendees. On the first worksheet I have a list of all attendees and based on what type of employee they are, it populates different worksheets in the workbook. There is a possible 400 attendees but i need the worksheets to hide the rows if there are not that many, ie if there is 10 type A employees rows 11-400 are hidden. Currently I have

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
Rows("7:400").Hidden = (Target.Value = "0")
Rows("8:400").Hidden = (Target.Value = "1")
etc.
End If
End Sub

Is there a way to loop this so I do not have to input 400 lines of code?
Also, is there a way to check the target cell every time the page is open so if like before there was 10 type A people and someone was added to the original worksheet that this page, when clicked on, would unhide the correct rows?

Thanks

 

[Davis10]

Yes, I have formulas in place that copies the data from the master tabs.

And yes, I want to hide the unused rows on all of the individual sheets.

Something in the background would seem like it would work fine and be exactly what I need.

 

[Joe4]

Yes, I have formulas in place that copies the data from the master tabs.

When you say formulas, do you mean macros?

If it just formulas with no macros/VBA, I would be very curious to know how it works (also because if it isn't VBA, getting the proposed to run in the background automatically could be a little tricky). So I kind of need to know how it is running to see if we can attach it to some current process.

 

[Davis10]

They are just reletively simple =IF statements and =COUNTIF statements

 

[Joe4]

I am trying hard to understand how exactly your process is working so we can come up with a solution that will work for you.

So, do you have, on every sheet, a formula that looks back to the master sheet at every row, meaning that not only do you have blank rows at the bottom of each sheet, but you may also have blank rows in the middle of your data too?

If so, are you trying to hide those rows too?

Lastly, how is the data on the master sheet populated?

 

[Davis10]

Yes on every sheet the formula looks back to the master sheet but there are not blank rows in the middle of the data because formula is written so it fills in the next row (I dont know how exactly it does this, I didnt write these formulas). The master sheet is populated by hand. The user enters each persons name and information.

 

[Joe4]

OK, I think we can actually do this using the Worksheet_Change event procedure on the Master sheet, like you had originally.

Assuming that your master sheet is always the first sheet, then I think this should work. It will run automatically whenever any change is manually made anywhere to the master sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim i As Long
    Dim myLastRow As Long
    
'   Loop through all worksheets, skipping the first (master)
    For i = 2 To Worksheets.Count
'   First unhide all rows
        Sheets(i).Rows("1:400").Hidden = False
'   Find last row with data
        myLastRow = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
'   Then hide all blank rows at bottom
        Sheets(i).Rows(myLastRow + 1 & ":400").Hidden = True
    Next i
 
End Sub

 

[Davis10]

The code works, however it is hiding the columns on the master sheet and not the other worksheets.

 

[Joe4]

Not sure why it wouldn't work for you, it is working for me.

Try this slight variation, where we actually activate the other sheets first before we work on them.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim i As Long
    Dim myLastRow As Long
    
    Application.ScreenUpdating = False
    
'   Loop through all worksheets, skipping the first (master)
    For i = 2 To Worksheets.Count
        Sheets(i).Activate
'   First unhide all rows
        Sheets(i).Rows("1:400").Hidden = False
'   Find last row with data
        myLastRow = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
'   Then hide all blank rows at bottom
        Sheets(i).Rows(myLastRow + 1 & ":400").Hidden = True
    Next i
 
    Sheets(1).Activate
    Application.ScreenUpdating = True
 
End Sub

If it still doesn't work for you, then answer following questions.

Is your master sheet the first sheet in your workbook?
What is the name of the master sheet (tab name)?
Have you placed the code in the worksheet module for the master sheet?

 

[Joe4]

Here's a slightly different variation if your master sheet is not sheet 1.
This assumes the name of your master sheet is "Master".

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws As Worksheet
    Dim myLastRow As Long
    
    Application.ScreenUpdating = False
    
'   Loop through all worksheets, skipping the first (master)
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            ws.Activate
'   First unhide all rows
            ws.Rows("1:400").Hidden = False
'   Find last row with data
            myLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
'   Then hide all blank rows at bottom
            ws.Rows(myLastRow + 1 & ":400").Hidden = True
        End If
    Next ws
 
    Sheets("Master").Activate
    Application.ScreenUpdating = True
 
End Sub