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

 

[Joe4]

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.

Why not just have it find the last row with data in it, and hide the rest (rather than check each one individually)?

 

[Davis10]

That would probably be much easier, but I do not know how to do that.

 

[Joe4]

Which column can we use to determine where the last row of data is (i.e. a column that always has to be populated for each row of data)?

 

[Davis10]

Column A can be used to determine that.

 

[Joe4]

    Dim myLastRow
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Rows(myLastRow & ":400").Hidden = True

 

[Davis10]

Hmm I cannot get it to work properly...any ideas as to what I should put as the Private Sub?

Or any other reasons it may not work?

 

[Joe4]

I can try to recreate your situation, but I'll need the following to do so:
- explain what the data looks like at first (post a small sampling, maybe)
- explain what you are doing that should call your code
- explain what your expected result is
- explain what is actually happening

 

[Davis10]

In columns A, B, C, D is the Name, Address, State and Zip Code, respectively. On a previous worksheet, in the workbook, there is a master list of names and an employee code which designates which worksheet they will populate to. My expected result is to populate the rows on the specific employee worksheets and hide the rows which are not shown. If there is a possible number of 400 attendees but if only 50 populate a certain worksheet then the rows 51-400 need to be hidden. It also needs to be able to update if names were added to the original list. An example would be like the 50 people from before and having rows 51-400 hidden but then someone was added to the original worksheet which falls in the same category so now the worksheet needs to have row 51 visible and hide rows 52-400. Currently I cannot get anything to happen.

 

[Joe4]

So, do you have some sort of process that is actually doing the copying of the data from the master tabs into your other tabs?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
And are you saying that you want to hide these unused rows on all of the individual employee worksheets?
<o> </o>
If so, I don’t think that you want to use the Worksheet_Change event procedure, as that one runs when something on that particular sheet is changed (so you would need that code on each sheet). Rather, I think it would work better. To have some VBA code that runs on the back-end of your data move process that loops through all the sheets and hides the appropriate rows, using the code I provided.
<o> </o>
Does it sound like that would work in your situation?