Forum Discussion
Hiding and automatically unhiding row
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
r = Cells.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 SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
Warning: the code will disable Undo.
- I have the same issue as RichardJ0958 above. I have copied the code above into "View Code" in the worksheet module but it is not working for me. Can you help me find out what I am doing wrong?
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
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
