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

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



Just change the if statement if you want something better.

[gcomyn]

Here is a slight change to the above code:


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



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


If Target.Row + 2 < Range("total_Hours").Row Then

[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
:sleuth:

[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
:sleuth:

[gcomyn]

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


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


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

GComyn
:sleuth:

[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
:sleuth:

[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?

[gcomyn]

can you attach a copy of your workbook? I'm not exactly sure about what you are talking about.

GComyn
:sleuth:

[gcomyn]

I'm not sure what you are talking about.... could you attach a copy of your workbook so we can take a look at it?

GComyn
:sleuth:

[wilg]

Hi gcomyn. Sorry for delay. I figured it out. What I had done was put a list in the cell that we would key the name. The list had the names of all the employees. But if I just deleted the name I guess "isempty" did not recognize it was truly empty. So I modified the code so that if target.value = "Hide Row" then run the hide row line. It works perfect now. Thank you so much for you help on this.

[wilg]

Hi there. This code you all helped me on is great and again I can't thank you enough. I need a minor edit though. If the cell above has a name in it can I get help with the line to goto the else statement? I want the user to have the cell below the name they just keyed available but can hide the cell 2 cells below. My thoughts were....

if (target.value -2) <> "" then

but I can't get it to work.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERROR
If (Target.Column = 1) Then

If Target.Row + 2 < Range("a326").Row Then
If (Target.Value) = "" Then

Call MacroUprotectAll
Rows((Target.Row) & ":" & (Target.Row + 1)).EntireRow.Hidden = True

Else
Call MacroUprotectAll

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

End If
End If
End If

Target.Select

Call ProtectAll

ERROR:
End Sub