Forum Discussion
Hiding and automatically unhiding row
I received your private message.
The cause of the problem is that you have formulas in G5:G200, so the code always sees row 200 as the last row.
The following should work:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
r = Range("A:F").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.EnableEvents = False
Cells(r + 1, 1).EntireRow.Hidden = False
Range(Cells(r + 2, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
Application.EnableEvents = True
End SubHans, the code works perfectly! Thanks for your help! Now, is there some code that I can add to your code to unprotect the spreadsheet, then unhide a line, and then protect the sheet again? I tried adding code myself but of course it did not work...I am not very good. Thanks for your help again.
Frank
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Long r = Range("A:F").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Application.EnableEvents = False Me.Unprotect Password:="secret" Cells(r + 1, 1).EntireRow.Hidden = False Range(Cells(r + 2, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True Me.Protect Password:="secret" Application.EnableEvents = True End SubReplace secret with the password used to protect the sheet.
If you want to protect the sheet without setting a password, omit both occurrences of Password:="secret"
Hans, I sent you a private message with a copy of my spreadsheet because I could not get the code to work. Never mind...I figured out the problem and it works correctly! Thanks for your help!