Solved: Hide unhide rows in range

[wilg]

Hi, new to site. But have been researching code many times as a guest. I am having much dificulty in my spreadsheet in wanting to do the following.
I have a employee schedule in that each employee has 2 rows each to key their shift by day and any comments in row below for them.
What I need is approx 100 rows available but at times when I only have say 60 employees to hide any rows that there is no name 2 rows below the last name on the sched. So If I key a new name on the last empty row, 2 more rows will pop open and so on. Any help is very much appreciated.

[Simon Lloyd]

We would need to see your workbook, by default there are always empty rows available to you so you wouldn't need to "pop them open"

[wilg]

Hi Simon, sorry about the cross post, realized I was posting in wrong area first one. Won't happen again. My empyt rows have formulas in them which calculate hours etc and then at the 100th row totals by day. It's the empyt rows between the last name in the sched and the last 100th row I would like to hide until a new name is keyed and then the next 2 rows unhide etc.

[Simon Lloyd]

Again we need to see the worksheet, what if the rows were hidden how would you add another name? where would you add the name?.....etc

[wilg]

I have attached an example. Out of all the employee rows I would like under the last employees name we keyed to have the next two rows unhidden. Then when I key in a new name in that last row, unhide the next 2 so its available for when I need to key a new name etc...Thanks for your patience and help.

[leigao84]

Edit: oh never-mind I see what you mean.
I don't think hidden is correct. What you can have is have excel resize the table object dynamically (depends if the 2nd to last row is empty), and execute this on the event when worksheet_change.

[leigao84]

Try this code, make sure it's under the worksheet
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Column = 2) Then

Rows((Target.Row + 2) & ":" & (Target.Row + 3)).Select
Selection.EntireRow.Hidden = False
Target.Select

End If

End Sub

[/vba]

Just change the if statement if you want something better.

[gcomyn]

Here is a slight change to the above code:

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1) Then
If Target.Row + 2 < Range("total_Hours").Row Then
Rows((Target.Row + 2) & ":" & (Target.Row + 3)).EntireRow.Hidden = False
End If
Target.Select
End If
End Sub

[/vba]

The previous code didn't do anything until you put in the first time (column 2)... from what you said above, if you change that to a 1 (the name field), then when you type in a name, the next 2 rows will be "unhidden"... that would work by itself, but it would try to unhide the total hours rows as well... with my code, it stops when it reaches that location. I've added a named range for A216 called "Total_Hours"... that way, you can add more rows, if needed, and not having to change the code once the total hours row changes.

GComyn

[wilg]

Hi guys, I tried putting the code in the worksheet and then I hid all the rows except for the first 4. When I added names ZI got a
"runtime error 1004 Method 'Range' of object_worksheet failed."
did you guys successfully make it work on the attachment I uploaded? Do I have to alter the code in any other way?

[Aussiebear]

It doesn't like the row

[vba]
If Target.Row + 2 < Range("total_Hours").Row Then
[/vba]

[wilg]

I assume by you saying doesnt like the row you are getting same runtime error?. On thing i noticed is "total _hours" should be "TOTAL_HRS" which I changed. but still get the runtime error.

[Simon Lloyd]

The Range("total_hours").Row will be 1!, as the rows in the range "total_hours" haven't been counted.

[gcomyn]

Total_hours is a named range... go to the sheet, click on A216, and in the menu area beside the function area (where it shows A216) type 'Total_Hours'. If there is no named range, then it will not work.

GComyn

[Simon Lloyd]

I didn't check the actual workbook, i'm just aware that you cannot use the .Row property for a named range that spans more than one cell as it will always give you the first row of the range, why name a single cell? surely for understanding it would be better to call it what it is "A216"?

[wilg]

You guys are awsome, works great. THANK YOU SOOOO much.
But now it there a way to reverse it if I delete a name? Do I just revers the code?

[gcomyn]

I named that single cell because I was toying with a way to add rows if the row 214 ever got filled... but I got so deep into recursion checking that I gave it up.

GComyn

[gcomyn]

Here is the revised code to hid rows when you delete a name:

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column = 1) Then
If Target.Row + 2 < Range("total_Hours").Row Then
If IsEmpty(Target.Value) Then
Rows((Target.Row + 2) & ":" & (Target.Row + 3)).EntireRow.Hidden = True
Else
Rows((Target.Row + 2) & ":" & (Target.Row + 3)).EntireRow.Hidden = False
End If
End If
Target.Select
End If
End Sub
[/VBA]

I've tested it, and it works for me...

GComyn

[wilg]

Hi gcomyn. sorry for late reply but I was trying to figure this out further myself. I can't get your reverse code to work. When I delete a name the next rows still unhide, not hide. I would like it if I delete the name from the cell that that row with the name I just deleted will hide. Any further suggestions are very much appreciated.

[gcomyn]

hmm... I don't know what is wrong... i tested it several times before posting, so it does work....

did you put the range name for cell A216? if you didn't, change range("total_hours") to range("A216"), and that might help.

GComyn

[wilg]

Ahhh, I realized that when I adapted my sched I was using the code in a cell that had a drop down menu to find the name. When I deleted the name your code didn't recognize that it was empty. But if I added a blank name to my list your code worked. Interesting. But is there any way to manipulate that when I delete the name it will hide the row instead of when I select the blank name from my list?