Hiding and automatically unhiding row

[RichardJ0958]

My worksheet has numerous rows that may not be used each particular time I open it.  I want to be able to keep the descending rows hidden until text is inserted into the row above it.  That is, say only row A6 is visible on opening the worksheet., and rows A7.A100 are hidden.  When the user enters text in A6, only then will A7 automatically become unhidden, and so forth down the page. Is there a way to set this up, say a formula in conditional formatting? 

[#2]

@RichardJ0958 

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 Sub

Switch 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.

[#3]

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?

[#4]

@ftindall 

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.

[#5]

@ftindall 

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 Sub

[#6]

@Hans Vogelaar 

Hans, 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

[#7]

@ftindall 

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 Sub

Replace 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"

[#8]

@Hans Vogelaar 

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!