Forum Discussion

RichardJ0958's avatar
RichardJ0958
Copper Contributor
Jun 02, 2023

Hiding and automatically unhiding row

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? 

excel

7 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 02, 2023

    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.

    • ftindall's avatar
      ftindall
      Copper Contributor
      Jan 11, 2024
      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?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 11, 2024

        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

Resources